[Oracle]오라클 LOCK 조회 및 해제

1. 간단하게 LOCK 확인

SELECT OBJECT_ID
     , SESSION_ID       -- SID
     , ORACLE_USERNAME
     , OS_USER_NAME
  FROM V$LOCKED_OBJECT
;

 

2. LOCK 발생된 SID, SERIAL, OBJECT 확인

SELECT A.SID
     , A.SERIAL
     , object_name
     , A.SID || ', ' || A.SERIAL# AS KILL_TASK
  FROM V$SESSION A
 INNER JOIN V$LOCK B ON A.SID = B.SID
 INNER JOIN DBA_OBJECTS C ON B.ID1 = C.OBJECT_ID
 WHERE B.TYPE  = 'TM'
;

 

3. LOCK 세션 해제

ALTER SYSTEM KILL SESSION '401, 12761'
;

 

4. LOCK 발생 사용자, OBJECT, 실행쿼리까지 조회

SELECT DISTINCT T1.SESSION_ID
     , T2.SERIAL
     , T4.OBJECT_NAME
     , T2.MACHINE
     , T2.TERMINAL
     , T2.PROGRAM
     , T3.ADDRESS
     , T3.PIECE
     , T3.SQL_TEXT
  FROM V$LOCKED_OBJECT T1
 INNER JOIN V$SESSION T2 ON T1.SESSION_ID = T2.SID
 INNER JOIN V$SQLTEXT T3 ON T2.SQL_ADDRESS = T3.ADDRESS
 INNER JOIN DBA_OBJECTS T4 ON  T1.OBJECT_ID = T4.OBJECT_ID
 WHERE 1=1
 ORDER BY T3.ADDRESS, T3.PIECE
;