Oracle Lock 확인 및 해제

>>락 확인

SELECT A.SID

  , A.SERIAL#

  , A.USERNAME

  , A.PROCESS

  , B.OBJECT_NAME

  , DECODE (C.LMODE, 2, 'RS', 3, 'RX', 4, 'S', 5, 'SRX', 8, 'X', 'NO') TABLE_LOCK

  , DECODE (A.COMMAND, 2, 'INSERT', 3, 'SELECT', 6, 'UPDATE', 7, 'DELETE', 12, 'DROP', 6, 'LOCK', 'UNKNOWN') SQL

  , DECODE (A.LOCKWAIT, NULL, 'NO WAIT', 'WAIT') STATUS

FROM   V$SESSION A, DBA_OBJECTS B, V$LOCK C

WHERE  A.SID = C.SID

AND    B.OBJECT_ID = C.ID1

AND    C.TYPE = 'TM'

AND    A.USERNAME = 'USER_NAME';

 

* 사용자 아이디는 대문자

SELECT SUBSTR(C.OBJECT_NAME,1,20),A.SID,A.SERIAL#

FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C

WHERE A.SID = B.SID

 AND B.ID1 = C.OBJECT_ID

 AND B.TYPE='TM';

 

>>락 해제

ALTER SYSTEM KILL SESSION '119, 16917'; --sid, serial# 

 

출처 : http://defree.co.kr/blog/?p=500