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;
