Thursday, August 5, 2010

Drop temporary tablespace hang with "enq: TS - contention"

When i drop the temporary tablespace, the SQL command hangs.

After further check, it waits for "enq: TS - contention".

SQL> select sid,event,seconds_in_wait from v$session where username='DONGHUA' and status='ACTIVE';

SID EVENT SECONDS_IN_WAIT
---------- ---------------------------------------- ---------------
44 enq: TS - contention 21

And blocked by "SMON".

SQL> select * from v$lock where request>0;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
CTIME BLOCK
---------- ----------
3E68104C 3E681078 44 TS 7 1 0 6
29 0


SQL> select sid from v$lock where id1=7 and id2=1;

SID
----------
13
44

SQL> select program,status from v$session where sid=13;

PROGRAM STATUS
------------------------------------------------ --------
oracle@vmxdb01.lab.dbaglobe.com (SMON) ACTIVE

SQL> select sid,event,seconds_in_wait from v$session where sid=13;

SID EVENT SECONDS_IN_WAIT
---------- ---------------------------------------- ---------------
13 smon timer 87


Check which session is still using the "TEMP2"

SQL> SELECT se.username username,
2 se.SID sid, se.serial# serial#,
3 se.status status, se.sql_hash_value,
4 se.prev_hash_value,se.machine machine,
5 su.TABLESPACE tablespace,su.segtype,
6 su.CONTENTS CONTENTS
7 FROM v$session se,
8 v$sort_usage su
9 WHERE se.saddr=su.session_addr;

USERNAME SID SERIAL# STATUS SQL_HASH_VALUE
------------------------------ ---------- ---------- -------- --------------
PREV_HASH_VALUE MACHINE
--------------- ----------------------------------------------------------------
TABLESPACE SEGTYPE CONTENTS
------------------------------- --------- ---------
DONGHUA 41 259 INACTIVE 0
2640221370 WORKGROUP\ORACLE-PC
TEMP2 LOB_DATA TEMPORARY


After kill it, the problem resloved.

SQL> alter system kill session '41,259';

System altered.