ORACLE 락 정보
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 LOCK 걸린 개체 확인 및 LOCK 해제
- 2 락 발생 사용자 및 OBJECT 조회 + 어떤 sql 를 실행중하여 lock 을 걸고 있는지 확인
- 3 락트리 보기
- 4 테이블 별 LOCK 확인
- 5 Lock을 잡고있는 세션과 기다리는 세션 조회
- 6 작업 중인 데이터베이스 트랜잭션 조회
- 7 락 발생시 같이 실행된 세션/SQL 조회
- 8 잠금 발생 유형 조회
- 9 잠금 상태 오브젝트 조회
- 10 LOCK 잠금 SQL 구문 조회
- 11 해당 테이블의 세션을 제거하는 쿼리 KILL / DISCONNECT
- 12 Blocking Lock Session 확인
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;