[oracle] 오라클 LOCK 걸린 개체 확인 및 해제

오라클이나 다른 DBMS를 사용 시 LOCK이 걸리는 현상이 있습니다.

TOAD 나 ORANGE 같은 상용제품을 사용할 때는 모니터링 툴이 있으니 바로 열어서 확인해볼 수 있지만

SQL Developer 같은 무료제품을 사용할 때에는 쿼리 구문을 통해 몇 가지를 확인을 해야 합니다.

 

1. LOCK 걸린 개체 확인하기

SELECT A.SID     --세션ID
     , 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';

SID  	SERIAL#	  OBJECT_NAME  	KILL_TASK
------------------------------------------------------------
400	  11234   TABLE_TEST	400, 11234

Object_name으로 해당 TABLE이 LOCK이 걸린 것을 확인할 수 있습니다.
SID, SERIAL#로 LOCK이 걸린 테이블의 SESSION을 KILL 합니다.

 

2. SID, 시리얼 번호로 세션 해제

 ALTER SYSTEM KILL SESSION 'SID, 시리얼번호';
 
 ALTER SYSTEM KILL SESSION '400, 11234';

락이 걸린 해당 테이블의 SID와 시리얼 번호를 이용하여 세션을 해제(KILL)합니다.

 

3. 락 발생 사용자, 오브젝트 조회, SQL조회 확인

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

위와 같이 조회하면 어떤 SQL문이 실행 중에 락이 걸린 지 확인할 수 있습니다.

 

4. 락 해제 쿼리문 자동작성

SELECT DISTINCT
  '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;

위와 같이 조회를 하면 세션을 해제하는 쿼리가 나옵니다..

행에 있는 걸 복붙해서 실행만 해주면 락 해제가 됩니다.