오라클 락 확인

 

-- 특정 테이블에 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 ('TB_CO_GENO');

 

-- 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;

 

-- 락발생 사용자와 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

2. 다음 명령으로 SESSION들을 KILL한다. ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';
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;