Cleaning up TEMP tablespace.

Hi List

On one of our databases, the Temp TS is currently 13,5 GB.

The tablespace is type TEMPORARY.

DB version is 9.2.0.4.0 running on AIX RS 6000.

It is a produktion database and bouncing the database should be avoided if possible.

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status

FROM v$session a,v$sort_usage b

WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

Returns no rows.

I know that since the TEMP TABLESPACE is of type TEMPORARY, the space is not released until the instance is shutdown.

SMON only cleans up when the TABLESPACE is of type PERMANENT.

There are 2 options, that I would like an opinion on.

1. Bounce the database.
2. Running SQL>alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';

Will the event take forever or can I expect it to finish within 1 hour ? Option 2 is what I would recommend in

situations where the size is more reasonable - say up to 1GB.

A bonus question:

What does a 13,5 GB large datafile / TEMP tablespace do to performance ? Will it be significant or is it merely a

space issue on the SAN.  

Thanks in advance,

Frank B Hansen    

--
http://www.freelists.org/webpage/oracle-l