Expert Tips on Drop Temporary Tablespace Hangs!!

Sometimes it happens that we issue the command for dropping temporary tablespace and the operation just hangs and we keep on waiting for a long time. In this article we will discuss how to resolve the frustrating situation when drop temporary tablespace hangs.

Oracle V$SORT_USAGE view tells you the space utilized within one temporary segment on a session and statement level. An entry is made in the V$SORT_USAGE table whenever an operation uses sort space. Once the operation is complete then that entry is automatically removed. However if any operation is aborted abnormally and dead connections are left then a number of entries are left in the V$SESSION and V$SORT_USAGE. Although one instance has only one sort segment per temporary tablespace but the space within the segment can be used by multiple statements running in different sessions.

Solution – I:

• Find Session Number from V$SORT_USAGE:

First of all you will have to run below command

SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

• Find Session ID from V$SESSION:

If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
OR
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

• Kill Session:

Now kill the session with IMMEDIATE.

ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

• Check Each Instance on RAC:

If you are working on RAC then the final step will be to check each Instance on RAC.

Solution – II:

• Create New Temporary Tablespace:

First of all you will have to create a new temporary tablespace.

CREATE TEMPORARY TABLESPACE MYTEMP2 TEMPFILE '/ORADATA2/ MYTEMP02.DBF' SIZE 100M;

• Assign all users to new Tablespace:

Now you will assign all users to this new tablespace.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE MYTEMP2;

• Find Inactive Sessions:

Now you will find inactive sessions in V$SORT_USAGE table by running below command.

SELECT A2.TABLESPACE, A2.SEGFILE#, A2.SEGBLK#, A2.BLOCKS, A1.SID, A1.SERIAL#, A1.USERNAME, A1.OSUSER, A1.STATUS FROM V$SESSION A1,V$SORT_USAGE A2 WHERE A1.SADDR = A2.SESSION_ADDR;

• Kill Inactive Sessions:

Now you will kill the inactive sessions by using SID and SERIAL# from previous resultset.

ALTER SYSTEM KILL SESSION 'SID_NUMBER, SERIAL#';

• Drop Previous Tablespace:

Finally you will have to drop the previous tablespace.

DROP TABLESPACE PREVIOUS_TEMP_TBSINCLUDING CONTENTS AND DATAFILES;