[Oracle] 오라클 락(lock) 걸렸을 때 사용할 유용한 쿼리들

오라클 락 걸렸을 때 유용하게 사용할 쿼리들입니다.

* 락걸린 테이블 확인

SELECT  DO.OBJECT_NAME, DO.OWNER, DO.OBJECT_TYPE, DO.OWNER, VO.XIDUSN, VO.SESSION_ID, VO.LOCKED_MODE
 FROM  V$LOCKED_OBJECT VO, DBA_OBJECTS DO
WHERE  VO.OBJECT_ID = DO.OBJECT_ID

* 해당 테이블에 LOCK 이 걸렸는지 확인

SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME
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' AND C.OBJECT_NAME IN ('T_SKUMA')

* 락발생 사용자와 SQL, OBJECT 조회

SELECT DISTINCT X.SESSION_ID, A.SERIAL#, D.OBJECT_NAME, A.MACHINE, A.TERMINAL,
                A.PROGRAM, B.ADDRESS, B.PIECE, B.SQL_TEXT
FROM V$LOCKED_OBJECT X, V$SESSION A, V$SQLTEXT B, DBA_OBJECTS D
WHERE X.SESSION_ID = A.SID AND 
X.OBJECT_ID = D.OBJECT_ID AND A.SQL_ADDRESS = B.ADDRESS 
ORDER BY B.ADDRESS, B.PIECE

* 현재 접속자의 SQL 분석

SELECT DISTINCT A.SID, A.SERIAL#,
       A.MACHINE, A.TERMINAL, A.PROGRAM,
       B.ADDRESS, B.PIECE, B.SQL_TEXT
FROM  V$SESSION A, V$SQLTEXT B
WHERE A.SQL_ADDRESS = B.ADDRESS
ORDER BY A.SID, A.SERIAL#, B.ADDRESS, B.PIECE

* 락 세션 죽이기

SELECT 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'
AND C.OBJECT_NAME = 'TB_CO_GENO'

SID SERIAL#
--- -------
5   1
6   1

결과값이 위와 같은 경우, 아래와 같이 kill한다.

SQL> alter system kill session '5, 1';
SQL> alter system kill session '6, 1';

* 락 세션 죽이는 sql 문

SELECT DISTINCT X.SESSION_ID, A.SERIAL#, D.OBJECT_NAME, A.MACHINE, A.TERMINAL,
       A.PROGRAM, A.LOGON_TIME, 'ALTER SYSTEM KILL SESSION'''||A.SID||', '||A.SERIAL#||''';'
FROM GV$LOCKED_OBJECT X, GV$SESSION A, DBA_OBJECTS D
WHERE  X.SESSION_ID = A.SID AND X.OBJECT_ID = D.OBJECT_ID
ORDER BY LOGON_TIME