오늘 정기점검 중 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
