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.

4 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
  4. This article gives the light in which we can observe the reality. This is very nice one and gives indepth information. Thanks for this nice article. Unlawful Termination Application

    ReplyDelete