Oracle Lock 확인

[Oracle] Lock 확인 및 Lock 해제  Oracle   

https://blog.naver.com/kcufl/60091779649
Lock확인 및 Lock해제는 sys, system 계정으로 sqlplus 및 toad 접속하여 아래의 명령을 실행한다.
 
1. Lock 걸린 테이블 및 세션 확인
 
1) SID, 시리얼번호, 테이블명 확인
SELECT
    a.sid, a.serial#, a.username, a.process, b.object_name,
    DECODE(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
    DECODE(a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
    DECODE(a.lockwait, NULL,'NO wait','Wait') "STATUS"
FROM
    v$session a, dba_objects b, v$lock c
WHERE
    a.sid=c.sid and b.object_id=c.id1
AND c.type='TM';

2) SID, 테이블명 확인
SELECT
    vo.session_id, do.object_name, do.owner, do.object_type,do.owner,
    vo.xidusn, vo.locked_mode
FROM
    v$locked_object vo , dba_objects do
WHERE
    vo.object_id = do.object_id;
 
3) 테이블명, Lock 상태 확인
SELECT 
    T1.object_name,
    DECODE(locked_mode, 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE',  4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', 'UNKNOWN') lock_mode
FROM 
    dba_objects T1, v$locked_object T2
WHERE
    T1.object_id = T2.object_id;
 
4) LOCK 상태와 세션 문자열 확인
 (출력정렬  스크립트)
set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username  format a10  heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18



SELECT
    nvl(S.USERNAME,'Internal') username,
    nvl(S.TERMINAL,'None') terminal,
    L.SID||','||S.SERIAL# Kill,
    U1.NAME||'.'||substr(T1.NAME,1,20) tab,
    decode(L.LMODE,1,'No Lock',
            2,'Row Share',
            3,'Row Exclusive',
            4,'Share',
            5,'Share Row Exclusive',
            6,'Exclusive',null) lmode,
    decode(L.REQUEST,1,'No Lock',
            2,'Row Share',
            3,'Row Exclusive',
            4,'Share',
            5,'Share Row Exclusive',
            6,'Exclusive',null) request
FROM
    V$LOCK L,
    V$SESSION S,
    SYS.USER$ U1,
    SYS.OBJ$ T1
WHERE       
    L.SID = S.SID
AND T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
AND U1.USER# = T1.OWNER#
AND S.TYPE != 'BACKGROUND'
ORDER BY 1,2,5
 
 
2. Lock 걸린 테이블 세션 제거
 
usage) ALTER SYSTEM KILL SESSION 'session ID, 시리얼번호'

ex) ALTER SYSTEM KILL SESSION '189,26359'

 

 

 

Lock 걸린 오브젝트 조회

SELECT OBJECT_ID
     , SESSION_ID       -- SID
     , ORACLE_USERNAME
     , OS_USER_NAME
  FROM V$LOCKED_OBJECT;

 

2. 해당 SID, Serial# 번호로 Lock 걸린 테이블 조회

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' --Table object type;

 

3. Lock 발생시킨 사용자 및 Object, Query(구문) 조회

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;

 

4. SID, Serial# 번호로 Session Kill 을 통해 Lock 해제

ALTER SYSTEM KILL SESSION '[sid], [serial#]';

-- ex
ALTER SYSTEM KILL SESSION '401, 12761';

 

 

 

[출처] [Oracle] Lock 확인 및 Lock 해제 |작성자 Real Iron