Cleaning up TEMP tablespace.
- From: Frank B Hansen <frank4oraclel@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 26 Oct 2004 15:33:48 +0200 (CEST)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
