[ORACLE] DB Lock 확인 쿼리

DB Lock 걸렸는지를 확인하는 절차를 아래의 쿼리를 순차적으로 처리하여 알 수 있다.

/* 1. check lock object */
SELECT OBJECT_ID
     , SESSION_ID
     , ORACLE_USERNAME
     , OS_USER_NAME
  FROM V$LOCKED_OBJECT;

/* 2. check sid, serial no , object name  */
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. kill sid */
ALTER SYSTEM KILL SESSION '401, 12761'; 

/* 4. check whoes locked object, sql text */ 
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
     , V$SESSION T2
     , V$SQLTEXT T3
     , DBA_OBJECTS T4
 WHERE 1=1
   AND T1.SESSION_ID = T2.SID
   AND T1.OBJECT_ID = T4.OBJECT_ID
   AND T2.SQL_ADDRESS = T3.ADDRESS
   ORDER BY T3.ADDRESS, T3.PIECE;