temporary tablespace drop 중의 wait event

오늘 정기점검 중 temporary tablespace의 크기가 너무 커져서

1. temporary tablespace 를 새로 생성하고
2. 데이터베이스의 default temporary tablespace를 변경하고
3. 기존 temporary tablespace를 drop 했다.

그런데 3번 temporary tablespace drop 하는 과정에서 너무 오랜 시간동안 지체되서 wait event를 살펴보니

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.

출처 : http://www.dbaglobe.com/2010/08/drop-temporary-tablespace-hang-with-enq.html