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.
Excellent post.
ReplyDeleteI could not get an article anyhwhere which covered on resolving the TS contention.
Thanks,
Thank Luo,
ReplyDeleteYour 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;
Thank you. Very helpful post :)
ReplyDeleteThis 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