Friday 14 September 2012

How do I reclaim space held by temporary segments in Oracle

Data being loading using direct path 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?

SQL> select segment_name ,segment_type from dba_segments where tablespace_name='TBS1!' ;

SEGMENT_NAME SEGMENT_TYPE
7.45678 Temporaray

you need to be use a drop segments event to remove temporary space from a tablespace:

First find the TS# value for the above tablespace

select TS# from v$tablespace where name='TBS1';
TS#
----
5

Then fire the event given below

ALTER SESSION SET EVENTS 'immediate trace name drop_segments level $a ';
where: a is the value for ts$ plus 1 here it would be 6:

SQL> select segment_name ,segment_type from dba_segments where tablespace_name='TBS1!' ;
no row selected


No comments: