오라클 테이블 락 확인 및 세션 종료

-- 락걸린 테이블 확인
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 ('테이블명');

-- 락발생 사용자와 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 = '테이블명';

ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';

-- 락 세션 죽이는 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;