How to reclaim space from temp

from http://www.dba-oracle.com/t_reclaim_space_temp_segment.htm

Question:  I was in the process of dropping a table when the instance crashed!  I now have a bunch of TEMP segments in the 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;

This command may remove a TEMP segment, try:

alter tablespace xxxxx coalesce

You can also use a drop segments event to remove temporary space from a 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;