Drop Temporary Tablespace Hangs With “enq: TS – contention”

After created a new temporary tablespace TEMPNEW as database default temporary tablespace, then try to drop the old temporary tablespace “TEMP”, it hangs up forever.

SQL> drop tablespace temp including contents and datafiles;

Check session wait event “enq: TS – contention” against TEMP tablespace.

SQL> select SID,EVENT,P2TEXT,P2,SECONDS_IN_WAIT,WAIT_CLASS 
       from v$session_wait 
      where sid=3413;

SID   EVENT                P2TEXT        P2  SECONDS_IN_WAIT WAIT_CLASS
----- -------------------- ------------- --- -------------- ----------
 3413 enq: TS - contention tablespace ID  2  973             Other

SQL> select TS#,name from v$tablespace where ts#=2;

TS#         NAME
---------- -----------
 2         TEMP

Check who is still using temporary tablespace TEMP.

SQL> select INST_ID,USERNAME,TABLESPACE,CONTENTS,SQL_ID,BLOCKS 
       from gv$SORT_USAGE;

INST_ID     USERNAME   TABLESPACE  CONTENTS  SQL_ID       BLOCKS
---------- ---------   ----------- --------- ------------ ----------
 1         USER1       TEMP        TEMPORARY 4xhb02sm7sgqk 128
 1         USER2       TEMP        TEMPORARY 8r74hjykx88y7 128
 1         USER1       TEMP        TEMPORARY 4xhb02sm7sgqk 128
 1         USER1       TEMP        TEMPORARY 8r74hjykx88y7 128
 1         USER2       TEMP        TEMPORARY cjjt4dw97qc1n 128
 1         USER2       TEMP        TEMPORARY 8r74hjykx88y7 128

6 rows selected.

Those user process are waiting for TS enqueue which is held by SMON.

SQL> SELECT SID,ID1,ID2,LMODE,REQUEST FROM V$LOCK WHERE TYPE='TS';

SID         ID1       ID2        LMODE       REQUEST
---------- ---------- ---------- ---------- ----------
 3413      2          1          0           6
 3413      2          0          6           0
 4261      7          2          3           0

SQL> SELECT PROGRAM FROM V$SESSION WHERE SID in (3413,4261);

PROGRAM
-----------------------------
sqlplus@ractest1 (TNS V1-V3)
oracle@ractest1 (SMON)

SOLUTION

1. Bounce the database.
2. then drop the temporary tablespace successfully.