Thursday, August 11, 2016

Temporary tablespace takes ages to drop

A simple command like 'DROP TABLESPACE TEMP;' in a regular database can become excessively slow.  The reason behind this is quite simple, even if the users defaults to a different temporary tablespace there could still be sessions holding temporary segments there.  It's just a matter of finding them and killing them  to free the blocks on the temporary segments and you're good to go.

The operation of dropping the temporary tablespace looks like this on Enterprise Manager:

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 tablespace;

Sessions still reported using the target temporary tablespace have to be killed.

After it is unlocked the process runs immediately.

No comments: