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.
