Friday, July 10, 2009

DROP TEMP Tablespace

TEMP Tablespace suddenly grew up to 200GB. There might have been some strange sorting which autoextended the temp tablespace. Usually SMON cleans up the temp segment but resize of the temporary datafile failed.

There are two possible ways to handle this issue.
- Reduce the buffer cache size to decrease the time taken to clean up the segment and then resize the temp files. This would take a downtime by restarting the database in a restricted mode with small buffer cache size , wait for SMON to drop the sort segments , then restart the database normally. Resize would work now.

But I always prefer the second option to re-create the temp tablespace.
So planned to create a new tablespace and make it as the active temporary segment. 
Then drop the old temp tablespace.

When I tried to drop the old temp tablespace it was hanging.

Basically it is because of some session which were still using the old temp tablespace.
v$tempseg_usage will help to figure out the information. A combination with v$session can bring up the session information also.

select a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks
from v$session a, v$tempseg_usage b
where a.saddr = b.session_addr 
order by b.tablespace, b.blocks;

I found few session using the old temp tablespace.
Had to kill some and had to wait for some sessions to get completed itself.

I hope the above query helps.

Popular Posts