[Oracle] Lock 확인

• 락이 걸린 세션 확인

SELECT SID,SERIAL#,USERNAME,MACHINE,PROGRAM,BLOCKING_INSTANCE,BLOCKING_SESSION,EVENT,SECONDS_IN_WAIT
FROM V$SESSION
WHERE BLOCKING_SESSION IS NOT NULL
OR SID IN (SELECT BLOCKING_SESSION
FROM V$SESSION
WHERE BLOCKING_SESSION IS NOT NULL);

※ BLOCKING_SESSION : LOCK의 원인이 되는 세션

 

 

 

• LOCK TYPE, LMODE 포함 락이 걸린 세션 확인

SELECT S.SID,
       S.SERIAL#,
       S.USERNAME,
       L.TYPE,
       L.LMODE,
       S.BLOCKING_SESSION,
       S.EVENT,
       S.SECONDS_IN_WAIT
FROM V$LOCK L,
     V$SESSION S
WHERE L.SID=S.SID
AND L.TYPE='TM';

 

 

 

• OBJECT 정보 포함 락이 걸린 세션 확인

SELECT L.SESSION_ID,
    S.SERIAL#,
    S.USERNAME,
    S.MACHINE,
    S.PROGRAM,
    D.OBJECT_NAME,
    S.BLOCKING_INSTANCE,
    S.BLOCKING_SESSION,
    S.EVENT,
    S.SECONDS_IN_WAIT
FROM DBA_OBJECTS D,
  V$LOCKED_OBJECT L,
  V$SESSION S
WHERE L.SESSION_ID = S.SID
AND D.OBJECT_ID = L.OBJECT_ID
ORDER BY S.SECONDS_IN_WAIT;

 

 

---

• 원인이 되는 세션 종료

ALTER SYSTEM KILL SESSION 'SESSION_ID,SERIAL#';

-- 예시
ALTER SYSTEM KILL SESSION '66,51676';