Temp tablespace 100% full tips

Temp tablespace 100% full tips

Oracle Database Tips by Donald BurlesonApril 29, 2016

Question: What do I do when my TEMP tablespace becomes 100% full?  I see no free space in dba_free_space.

Answer:  The TEMP tablespace is used for large hash joins and sorts, operations that will not fit into the PGA. 

It is completely normal for the TEMP tablespace to show at 100% full, and this does not mean that it is out of space.    If you truly run out of space in the TEMP tablespace you will receive the ORA-01652 unable to extend temp segment error message.

Rather, Oracle does not incur the overhead of scrubbing the TEMP tablespace after each use, and only marks the space as being eligible for reuse. 

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
/