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.

3 comments:

  1. Excellent post.
    I could not get an article anyhwhere which covered on resolving the TS contention.

    Thanks,

    ReplyDelete
  2. Thank Luo,

    Your post saved me. I had a MMON background process holding on to the temporary ts while i was attempting to resize the temporary ts (drop and recreate).

    I didn't dare to terminate the MMON background process. And from your SQL, i managed to determine that I have a dbconsole session who is also having persistent reference to the temp ts.

    My question in seek of advise, do i need to logout all dbconsole sessions too before i drop temp ts? So, before i execute drop ts, i should check whether there is any user using the ts.

    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;

    ReplyDelete
  3. Thank you. Very helpful post :)

    ReplyDelete