행위

ORACLE 락 정보

DB CAFE

1 LOCK 걸린 개체 확인 및 LOCK 해제

1.1 lock 걸린 개체 확인

/* 12C 부터 WITH절에 함수,프로시져 사용 가능 
WITH FUNCTION DBADM.FN_SEC_TO_TIME(P_SEC NUMBER) RETURN VARCHAR2
AS
    V_TIME VARCHAR2(10);
BEGIN
    IF P_SEC IS NULL THEN
        V_TIME := NULL;
    ELSE
        V_TIME := TO_CHAR(TRUNC(P_SEC/3600), 'FM900')
                  ||':'||TO_CHAR(TRUNC(MOD(P_SEC, 3600) / 60), 'FM00')
                  ||':'||TO_CHAR(TRUNC(MOD(P_SEC, 60  )     ), 'FM00');
    END IF;
    
    RETURN V_TIME;
END;
*/

SELECT /*+ ordered */
                      LEVEL
                          lvl,
                      NVL (s.blocking_session, s.sid)
                          lck_hold,
                      CASE WHEN s.blocking_session IS NOT NULL THEN s.sid END
                          lck_wait,
                      s.username,
                      s.osuser,
                      s.status,
                      l.TYPE
                          TYPE,
                      fn_sec_to_time (l.ctime)
                          lock_tm,
                      DECODE (lmode,
                              0, 'NONE',
                              1, 'NULL',
                              2, 'row-S(SS)',
                              3, 'row-X(SX)',
                              4, 'share(S)',
                              5, 'S/Row-X(SSX)',
                              6, 'exclusive(X)')
                          HOLD,
                      o.name
                          object --, decode(l.id2, 0, null, trunc(l.id1 / power(2,16))) undo#
                                --, decode(l.id2, 0, null, bitand(l.id1, to_number('ffff', 'xxxx')) + 0 )  slot#
                                ,
                      l.id1,
                      l.id2,
                      DECODE (l.request,
                              0, 'NONE',
                              1, 'NULL',
                              2, 'row-S(SS)',
                              3, 'row-X(SX)',
                              4, 'share(S)',
                              5, 'S/Row-X(SSX)',
                              6, 'exclusive(X)')
                          REQUEST,
                      s.event,
                      s.machine,
                      s.module,
                      s.program,
                      s.sql_id,
                      s.blocking_session,
                      l.ctime,
                      o.obj#,
                      t.name
                          TYPE_NM,
                      t.id1_tag,
                      t.id2_tag,
                      s.sid,
                      s.serial#,
                         'ALTER SYSTEM KILL SESSION '''
                      || S.sid
                      || ','
                      || S.serial#
                      || ',@'
                      || S.INST_ID
                      || ''';'  CMD_KILL,
                         'ALTER SYSTEM DISCONNECT SESSION '''
                      || S.sid
                      || ','
                      || S.serial#
                      || ''' IMMEDIATE;'  CMD_DISCONNECT
                 FROM GV$lock l,
                      GV$session s,
                      sys.obj$ o,
                      GV$lock_type t
                WHERE     1 = 1
                      AND s.TYPE != 'BACKGROUND'
                      --  and s.username = 'EP_APP'
                      --  and s.sid = 4179
                      AND l.sid = s.sid
                      AND l.TYPE IN ('TM', 'TX', 'UL')
                      AND o.obj#(+) = DECODE (L.ID2, 0, L.ID1, -1)
                      AND t.TYPE = l.TYPE
           CONNECT BY     PRIOR s.sid = s.blocking_session
                      AND PRIOR l.id1 = l.id1
                      AND PRIOR l.id2 = l.id2
           START WITH s.blocking_session IS NULL
    ORDER SIBLINGS BY lock_tm DESC, lck_hold
--order by lck_hold, l.ctime desc, l.sid
;

1.2 해당 sid 와 serial 번호로 락걸린 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'
 ;

1.3 락걸린 OBJECT 세션 KILL

select 'ALTER SYSTEM KILL SESSION '''||a.sid||','||serial#||''';' SESS_KILL --세션 종료     
     , 'ALTER SYSTEM DISCONNECT SESSION '''|| a.SID|| ','|| a.SERIAL#|| ',@' || INST_ID || '''' || ' IMMEDIATE;' PS_KILL   -- 프로세스 KILL 
  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' -- DML락 , TX = 트랜잭션 
   and c.owner='OOADM'  -- OWNER명 
--   and c.object_name = 'TB_SA_BIZPLC_M'; -- 테이블명

alter system  kill session '45,74[,@1]' [IMMEDIATE]; -- []는 생략가능 , @1은 RAC일때 1번 인스턴스

1.3.1 DBA용 락 세션 KILL 뷰

CREATE OR REPLACE FORCE VIEW VW_DBA_LOCKS_KILL
AS
    SELECT B.SID
         , C.SERIAL#
         ,    'EXEC rdsadmin.rdsadmin_util.kill('
           || B.SID
           || ','
           || C.SERIAL#
           || ');'                       AWS_KILL_CMD              --     , B.ADDR
         , 'ALTER SYSTEM KILL SESSION '''||b.sid||','||c.serial#||''';' SESS_KILL --세션 종료     
         , 'ALTER SYSTEM DISCONNECT SESSION '''|| b.SID|| ','|| c.SERIAL#|| ',@' || b.INST_ID || '''' || ' IMMEDIATE;' PS_KILL   -- 프로세스 KILL 

         , B.BLOCK         -- 0 - The lock is not blocking any other processes
         -- 1 - The lock is blocking other processes
         -- 2 - The lock is not blocking any blocked processes on the local node, but it may or may not be blocking processes on remote nodes. This value is used only in Oracle Real Application Clusters (Oracle RAC) configurations (not in single instance configurations).
         --     , B.KADDR
         , C.STATUS
         , FN_SEC_TO_TIME (B.CTIME)      CTIME -- Time since current mode was granted
         , B.TYPE                                          -- TM - DML enqueue
         -- TX - Transaction enqueue
         -- UL - User supplied
         , B.LMODE                                     -- 세션이 잠금을 유지하는 잠금 모드 :
         -- 0 - 없음
         -- 1 - 널 (NULL)
         -- 2 - 행-S (SS)
         -- 3 - 행-X (SX)
         -- 4 - share (S)
         -- 5 - S / Row-X (SSX)
         -- 6 - exclusive (X)
         , B.REQUEST                                 -- 프로세스가 잠금을 요청하는 잠금 모드 :
         -- 0 - none
         -- 1 - null (NULL)
         -- 2 - row-S (SS)
         -- 3 - row-X (SX)
         -- 4 - share (S)
         -- 5 - S/Row-X (SSX)
         -- 6 - exclusive (X)
         , B.ID1
         , B.ID2
         , (SELECT OBJECT_NAME
              FROM DBA_OBJECTS
             WHERE OBJECT_ID = B.ID1)    TBL1
         , (SELECT OBJECT_NAME
              FROM DBA_OBJECTS
             WHERE OBJECT_ID = B.ID2)    TBL2                --     , C.OSUSER
         , C.MACHINE
         , C.PROGRAM
      --          D.SQL_TEXT
      FROM V$LOCK B 
 LEFT JOIN V$SESSION C 
        ON B.SID = C.SID
     --        LEFT JOIN V$SQLTEXT D ON C.SQL_ID = D.SQL_ID
     WHERE B.TYPE IN ('TM', 'TX')
--   AND B.SID IN (160,796,743,190,842)
;

2 락 발생 사용자 및 OBJECT 조회 + 어떤 sql 를 실행중하여 lock 을 걸고 있는지 확인

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
   ;

3 락트리 보기

SELECT level,
       LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s
WHERE  level > 1
OR     EXISTS (SELECT 1
               FROM   v$session
               WHERE  blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;

또는

SELECT s.blocking_session
     , TO_NUMBER (s.sid)               Waiting_Session
     , s.event
     , s.seconds_in_wait
     , P.pid
     , P.spid                          "ServerPID"
     , s.process                       "ClientPID"
     , s.username
     , s.program
     , s.machine
     , s.osuser
     , s.sql_id
     , SUBSTR (sq.sql_text, 1, 75)     SQL
  FROM v$sql sq
     , v$session s
     , v$process P
 WHERE     s.event LIKE 'enq: TX%'
       AND s.paddr = p.addr
       AND s.sql_address = sq.address
       AND s.sql_hash_value = sq.hash_value
       AND s.sql_id = sq.sql_id
       AND s.sql_child_number = sq.child_number
UNION ALL
SELECT s.blocking_session
     , TO_NUMBER (s.sid)               Waiting_Session
     , s.event
     , s.seconds_in_wait
     , p.pid
     , p.spid                          "ServerPID"
     , s.process                       "ClientPID"
     , s.username
     , s.program
     , s.machine
     , s.osuser
     , s.sql_id
     , SUBSTR (sq.sql_text, 1, 75)     SQL
  FROM v$sql sq
     , v$session s
     , v$process p
 WHERE  s.sid IN (SELECT DISTINCT blocking_session
                    FROM v$session
                   WHERE event LIKE 'enq:TX%')
       AND s.paddr = p.addr
       AND s.sql_address = sq.address(+)
       AND s.sql_hash_value = sq.hash_value(+)
       AND s.sql_id = sq.sql_id(+)
       AND s.sql_child_number = sq.child_number(+)
ORDER BY 1 NULLS FIRST, 2

4 테이블 별 LOCK 확인

SELECT A.SID
     , A.SERIAL#
     , SUBSTRB(A.USERNAME, 1, 16) AS USERNAME
     , SUBSTRB(A.MACHINE, 1, 30) AS MACHINE
     , A.TERMINAL
     , A.OSUSER
     , A.PROGRAM
     , SUBSTRB(TO_CHAR(A.LOGON_TIME, 'MM/DD HH24:MI:SS'), 1, 14) AS LOGON_TIME
     , SUBSTRB(C.OBJECT_NAME, 1, 58) AS 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 LIKE UPPER('&테이블명');

5 Lock을 잡고있는 세션과 기다리는 세션 조회

SELECT DECODE(B.LOCKWAIT, NULL, ' ', 'w') AS WW
     , B.SID
     , B.SERIAL# AS SER#
     , SUBSTR(B.MACHINE, 1, 10) AS MACHINE
     , SUBSTR(B.PROGRAM, 1, 15) AS PROGRAM
     , SUBSTR(A.OBJECT_NAME, 1, 17) AS OBJ_NAME
     , SUBSTR(B.STATUS, 1, 1) AS S
     , DECODE(B.COMMAND,  0, NULL,  2, 'INSERT',  6, 'UPDATE',  7, 'DELETE',  B.COMMAND) AS SQLCMD
     , B.PROCESS AS PGM_PSS
  FROM V$SESSION B
     , (SELECT    A.SID, DECODE(B.OWNER, NULL, A.TYPE || '..ing', B.OWNER || '.' || B.OBJECT_NAME) AS OBJECT_NAME
           FROM      V$LOCK A
                , DBA_OBJECTS B
           WHERE     A.ID1 = B.OBJECT_ID(+)
           GROUP BY  A.SID, DECODE(B.OWNER, NULL, A.TYPE || '..ing', B.OWNER || '.' || B.OBJECT_NAME)) A

 WHERE B.SID = A.SID AND B.TADDR IS NOT NULL;

6 작업 중인 데이터베이스 트랜잭션 조회

SELECT S.SID

     , S.SERIAL#
     , S.STATUS
     , S.OSUSER
     , S.USERNAME
     , T.STATUS
     , T.START_TIME
  FROM V$SESSION S
     , V$TRANSACTION T
     , DBA_ROLLBACK_SEGS R
 WHERE S.TADDR = T.ADDR AND T.XIDUSN = R.SEGMENT_ID;

7 락 발생시 같이 실행된 세션/SQL 조회

SELECT /*+ ORDERED */ A.SADDR, A.SID, A.USER_NAME, A.ADDRESS, A.HASH_VALUE, A.SQL_ID
       , to_single_byte(A.SQL_TEXT) -- 멀티바이트 오류때문에 to_single_byte 함수 사용
       , A.SQL_TEXT
       , A.LAST_SQL_ACTIVE_TIME, A.SQL_EXEC_ID, A.CURSOR_TYPE, A.CHILD_ADDRESS, A.CON_ID
   FROM V$OPEN_CURSOR A
   JOIN V$SESSION B
     ON B.SID = A.SID
    AND B.SQL_ID = :SQL_ID     
  ORDER BY A.LAST_SQL_ACTIVE_TIME DESC 
  ;

8 잠금 발생 유형 조회

SELECT A.SID
     , DECODE(A.TYPE
            , 'MR', 'MEDIA RECOVERY'
            , 'RT', 'REDO THREAD'
            , 'UN', 'USER_NAME'
            , 'TX', 'TRANSACTION'
            , 'TM', 'DML'
            , 'UL', 'PL/SQL USER LOCK'
            , 'DX', 'DISTRIBUTED XACTION'
            , 'CF', 'CONTROL FILE'
            , 'IS', 'INSTANCE STATE'
            , 'FS', 'FILE SET'
            , 'IR', 'INSTANCE RECOVERY'
            , 'FS', 'FILE SET'
            , 'ST', 'DISK SPACE TRANSACTION'
            , 'TS', 'TEMP SEGMENT'
            , 'IV', 'LIBRARY CACHE INVAILDATION'
            , 'LS', 'LOG START OR SWITCH'
            , 'RW', 'ROW WAIT'
            , 'SQ', 'SEQUENCE NUMBER'
            , 'TE', 'EXTEND TABLE'
            , 'TT', 'TEMP TABLE'
            , A.TYPE
                )
            AS "LOCK_TYPE"
     , DECODE(A.LMODE
            , 0, 'NONE'
            , 1, 'NULL'
            , 2, 'ROW-S(SS)'
            , 3, 'ROW-X(SX)'
            , 4, 'SHARE'
            , 5, 'S/ROW-X(SSX)'
            , 6, 'EXCLUSIVE'
            , TO_CHAR(A.LMODE)
                )
            AS "MODE_HELD"
     , DECODE(A.REQUEST
            , 0, 'NONE'
            , 1, 'NULL'
            , 2, 'ROW-S(SS)'
            , 3, 'ROW-X(SX)'
            , 4, 'SHARE'
            , 5, 'S/ROW-X(SSX)'
            , 6, 'EXCLUSIVE'
            , TO_CHAR(A.REQUEST)
                )
            AS "MODE_REQUESTED"
     , TO_CHAR(A.ID1) AS "LOCK_ID1"
     , TO_CHAR(A.ID2) AS "LOCK_ID2"
     , DECODE(BLOCK,  0, 'NOT BLOCKING',  1, 'BLOCKING',  2, 'GLOBAL',  TO_CHAR(BLOCK)) AS "BLOCKING_OTHERS"

  FROM V$LOCK A WHERE (ID1, ID2) IN (SELECT B.ID1, ID2

                         FROM      V$LOCK B
                         WHERE     B.ID1 = A.ID1);

9 잠금 상태 오브젝트 조회

SELECT A.SESSION_ID

     , B.SERIAL#
     , A.OS_USER_NAME
     , A.ORACLE_USERNAME
     , C.OBJECT_NAME
     , A.LOCKED_MODE
     , A.XIDUSN

  FROM V$LOCKED_OBJECT A

     , V$SESSION B
     , DBA_OBJECTS C

 WHERE A.OBJECT_ID = C.OBJECT_ID AND A.SESSION_ID = B.SID;

10 LOCK 잠금 SQL 구문 조회

SELECT B.USERNAME AS USERNAME
     , C.SID AS SID
     , C.OWNER AS OBJECT_OWNER
     , C.OBJECT AS OBJECT
     , B.LOCKWAIT
     , A.PIECE
     , A.SQL_TEXT AS SQL
  FROM V$SQLTEXT A
     , V$SESSION B
     , V$ACCESS C
 WHERE A.ADDRESS = B.SQL_ADDRESS 
   AND A.HASH_VALUE = B.SQL_HASH_VALUE 
   AND B.SID = C.SID AND C.OWNER!= 'SYS';

11 해당 테이블의 세션을 제거하는 쿼리 KILL / DISCONNECT

/*

   특정 테이블이 락을 발생하고 있으면 세션을 찾아서 중단시킨다.

*/ 
-- KILL
SELECT 'ALTER SYSTEM KILL SESSION '''|| S.SID||','||S.SERIAL# ||''';' KILL_CMD
     ,  'ALTER SYSTEM DISCONNECT SESSION '''|| S.SID||','||S.SERIAL# ||'''POST_TRANSACTION/IMMEDIATE;' DISCONNCT_CMD
  FROM V$LOCK L
     , DBA_OBJECTS O
     , V$SESSION S 
 WHERE L.ID1 = O.OBJECT_ID 
   AND S.SID = L.SID 
   AND O.OWNER = 'ESTDBA' 
   AND O.OBJECT_NAME = 'TMP_GSYM2'

-- DISCONNECT
SELECT 'ALTER SYSTEM DISCONNECT SESSION '''|| S.SID||','||S.SERIAL# ||'''POST_TRANSACTION/IMMEDIATE;' 
  FROM V$LOCK L, DBA_OBJECTS O
     , V$SESSION S 
 WHERE L.ID1 = O.OBJECT_ID 
   AND S.SID = L.SID 
   AND O.OWNER = 'ESTDBA' 
   AND O.OBJECT_NAME = 'TMP_GSYM2'

12 Blocking Lock Session 확인

SELECT B.BLOCKING_SESSION AS BLOCKING_SESSION_SID

     , C.SID AS LOCK_SESSION_SID
     , C.OWNER AS OBJECT_OWNER
     , C.OBJECT AS OBJECT
     , B.LOCKWAIT
     , A.PIECE
     , A.SQL_TEXT AS SQL

  FROM V$SQLTEXT A
     , V$SESSION B
     , V$ACCESS C
 WHERE A.ADDRESS = B.SQL_ADDRESS 
   AND A.HASH_VALUE = B.SQL_HASH_VALUE 
   AND B.SID = C.SID 
   AND B.BLOCKING_SESSION IS NOT NULL 
   AND C.OWNER NOT IN ('SYS', 'PUBLIC') 
   AND C.OBJECT NOT IN ('TOAD_PLAN_TABLE') ORDER BY A.PIECE;