행위

ORACLE 락 정보

DB CAFE

Dbcafe (토론 | 기여)님의 2019년 7월 7일 (일) 14:08 판
thumb_up 추천메뉴 바로가기

1 LOCK 걸린 개체 확인 및 LOCK 해제[편집]

  1. lock 걸린 개체 확인
SELECT OBJECT_ID
     , SESSION_ID       -- SID
     , ORACLE_USERNAME
     , OS_USER_NAME
  FROM V$LOCKED_OBJECT
;
  1. 해당 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. sid 와 시리얼 번호로 세션 해제
select  'ALTER SYSTEM KILL SESSION '''||a.sid||','||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.owner='OOADM' and c.object_name = 'TB_SA_BIZPLC_M'; --

alter system  kill session '45,74';

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;

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 열려 있는 커서 조회[편집]

SELECT A.SID
     , A.OSUSER
     , COUNT(B.SID) AS "CURSOR"
     , A.PROGRAM
     , A.STATUS
  FROM V$SESSION A
      , V$OPEN_CURSOR B

 WHERE A.SID = B.SID(+) GROUP BY A.SID, A.OSUSER, A.PROGRAM, A.STATUS;

== 잠금 발생 유형 조회 -- 


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

8 잠금 상태 오브젝트 조회[편집]

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;

9 잠금 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';

10 해당 테이블의 세션을 제거하는 쿼리[편집]

/*

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

*/ 

SELECT 'ALTER SYSTEM KILL SESSION '''|| S.SID||','||S.SERIAL# ||''';' 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'



== Blocking Lock Session 확인 ==

<source lang='sql'> 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;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=ORACLE_락_정보&oldid=1249"