How to reclaim space from temp

 

Question:  I was in the process of dropping a table when the instance crashed!  I now have a bunch of TEMP segments in a non-temporary tablespace.  How do I reclaim space held by temporary segments in Oracle?

Answer:  I would wait for SMON to clean it up when he coalesces the tablespace. The System Monitor background process (SMON) recovers after instance failure and monitors temporary segments and extents.

If you have purchased the packs to use the AWR, see dba_hist_undostat tips , which can help unveil how Oracle attempted to used all available undo before aborting with the ORA-01652 error.

You can check for held TEMP segments with this query:

select
   srt.tablespace,
   srt.segfile#,
   srt.segblk#,
   srt.blocks,
   a.sid,
   a.serial#,
   a.username,
   a.osuser,
   a.status
from
   see code depot for full scripts
   v$session    a,
   v$sort_usage srt
where
   a.saddr = srt.session_addr
order by
   srt.tablespace, srt.segfile#, srt.segblk#,
   srt.blocks;

You can also use a drop segments event to remove temporary space from a non-TEMP tablespace:

ALTER SESSION SET EVENTS 'immediate trace name drop_segments level &x';

where:

   x is the value for file# from ts$ plus 1.

The following query will display coalesce information:

SELECT
    tablespace_name,
    bytes_coalesced,
    extents_coalesced,
    percent_extents_coalesced,
    blocks_coalesced,
    percent_blocks_coalesced
FROM
    sys.dba_free_space_coalesced
ORDER BY
    tablespace_name;

---