오라클 세션락 총정리

1.락 걸린 테이블 확인하는 쿼리

SELECT
  dbo.object_name
  ,dbo.owner
  ,dbo.object_type
  ,vlo.xidusn
  ,vlo.session_id
  ,vlo.locked_mode
FROM V$LOCKED_OBJECT vlo, DBA_OBJECTS dbo
WHERE vlo.object_id = dbo.object_id;

2.특정 테이블이 락 걸렸는지 확인하는 쿼리

SELECT
  vs.sid
  ,vs.serial#
  ,vl.type
  ,dbo.object_name
FROM V$SESSION vs, V$LOCK vl, DBA_OBJECTS dbo
WHERE vs.sid = vl.sid
  AND vl.id1 = dbo.object_id
  AND vl.type = 'TM' 
  AND dbo.object_name IN ('테이블명');

3. 락을 발생한 사용자와 실행 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;

4. 락 걸린 사용자 제거(kill) 실행문 추출 쿼리 

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
;

ALTER SYSTEM KILL SESSION '26, 6044';


==========================================================================

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



============================================================================================


<Lock걸린 부분이 있는지 확인>
SELECT C.OBJECT_NAME
     , B.SID
     , B.SERIAL#
     , 'ALTER SYSTEM KILL SESSION '''||B.SID||','||B.SERIAL#||''';'
FROM   V$LOCKED_OBJECT A
JOIN   V$SESSION B ON ( A.SESSION_ID = B.SID )
JOIN   DBA_OBJECTS C ON ( A.OBJECT_ID = C.OBJECT_ID );
위 쿼리로 세션을 Kill
 
 
<만약 Lock 내역이 보이지 않는다면>
SELECT A.LOCK_ID1
     , A.SESSION_ID
     , 'ALTER SYSTEM KILL SESSION '''||B.SID||','||B.SERIAL#||''';'
FROM   DBA_LOCK_INTERNAL A
JOIN   V$SESSION B ON ( A.SESSION_ID = B.SID )
WHERE  LOCK_ID1 LIKE '%object_name%';  --> Object Name 입력
- Lock이 걸린 것으로 의심되는 object의 이름을 넣어 위 쿼리로 확인
- 내역이 보이면 세션을 Kill 시킨다.
 
 
<하지만 여전히 세션 내역에 존재하고>
SELECT STATUS
FROM   V$SESSION
WHERE  SID = 'session_id';  --> Session ID 입력
위 쿼리의 결과(STATUS)가 'Killed'라고 나온다면
 
 
<서버에서 돌고있는 Lock 관련 프로세스 확인>
SELECT B.SPID
     , 'KILL -9 ' || B.SPID
FROM   V$SESSION A
JOIN   V$PROCESS B ON ( A.PADDR = B.ADDR )
WHERE  A.SID = 'session_id';  --> Session ID 입력
 
 
<프로세스 ID가 실제 존재하는지 서버에서 확인>
ps -ef | grep 프로세스ID
 
 
<서버 프로세스 kill>
#DB서버
kill -9 프로세스ID
 
Kill 후 쿼리로 세션 존재여부 확인