In my Oracle Database TEMP tablespace becomes 100% full?

Large hash joins and sorts that won't fit in the PGA are performed in the TEMP tablespace.

The TEMP tablespace showing as 100% full is entirely normal and does not indicate that there is no more room available. The error message ORA-01652 unable to expand temp segment will appear if the TEMP tablespace actually runs out of space.

Instead, Oracle merely indicates that the TEMP tablespace is available for reuse rather than incurring the cost of cleaning it after every usage.

Try to use instead v$tempseg_usage to see the real temporary space allocated. This query will show the real temp space usage:

select 
   username, 
   blocks*(8192)/(1024*1024) mb 
from 
   v$tempseg_usage;

It is normal that dba_free_space shows no free space in temporary tablespace because space is managed differently from permanent tablespaces.

Hence, 100% full is normal and transactions will re-use the space as-needed.

Here is a script that will display the contents of the TEMP tablespace.

set pagesize 60 linesize 132 verify off 
break on file_id skip 1 

column file_id heading "File|Id" 
column tablespace_name for a15 
column object          for a15 
column owner           for a15 
column MBytes          for 999,999 
select tablespace_name, 
'free space' owner, /*"owner" of free space */ 
' ' object,         /*blank object name */ 
file_id, /*file id for the extent header*/ 
block_id, /*block id for the extent header*/ 
CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 
from dba_free_space 
where tablespace_name like '%TEMP%' 
union 
select tablespace_name, 
substr(owner, 1, 20), /*owner name (first 20 chars)*/ 
substr(segment_name, 1, 32), /*segment name */ 
file_id, /*file id for extent header */ 
block_id, /*block id for extent header */ 
CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 
from dba_extents 
where tablespace_name like '%TEMP%' 
order by 1, 4, 5 
/