Hi all
We are having oracle 9i and tablespaces are LMT
Recently we got following err msg in alert.
ORA-1652: unable to extend temp segment by 128 in tablespace
TEMP

I checked the following
Qry->1
-------
SELECT tablespace_name, extent_size, total_extents, used_extents,
free_extents, max_used_size FROM v$sort_segment;

TABLESPACE_NAME EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS MAX_USED_SIZE
------------------------------ ----------- ------------- ------------ ------------ -------------
TEMP 128 11140 0
11140 0

Qry->2
-------
SQL> select TABLESPACE_NAME,CONTENTS,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,PCT_INCREASE,EXTENT_MANAGEMENT,SEGME NT_SPACE_MANAGEMENT
2 from dba_tablespaces where tablespace_name like '%TEMP%';

TABLESPACE_NAME CONTENTS INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE EXTENT_MAN SEGMEN
------------------------------ --------- -------------- ----------- ----------- ------------ ---------- ------
TEMP TEMPORARY 1048576 1048576 0 LOCAL MANUAL

QRY-3
------
SQL> select tablespace_name, file_id, bytes_used, bytes_free
2 from v$temp_space_header ;

TABLESPACE_NAME FILE_ID BYTES_USED BYTES_FREE
------------------------------ ---------- ---------- - ---------
TEMP 1 5242880000 0
TEMP 2 3221225472 0
TEMP 3 2147483648 0
TEMP 4 1073741824 0

can you tell why in v$sort_segment it's showing free and in $temp_space_header it is showing bytes used as 0 and how to clear/deallocate temp segments in temp space.WE ARE RESTARTING THE DATABASE DAILY IN THE MORNING.

Regards