Wednesday, February 8, 2012

Dropping Old Temporary Tablespace Hangs

I have recently switched my database to a new temporary tablespace and wanted to drop the old one to retrieve the space it was occupying. But the drop statement seem to hang or is taking too long.

Here's my drop statement:

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

To solve this, I issued the following query:

SELECT b.username, b.sid, b.serial#, b.terminal
FROM v$sort_usage a, v$session
WHERE a.session_num = b.serial#
AND a.tablespace = 'TEMP';

Then I killed the sessions that were using the old temporary segment (of course I asked permission from the user before killing them).

After this, the hanged drop tablespace command continued and dropped the temp tablespace successfully.


No comments:

Post a Comment