"ORACLE 락 정보"의 두 판 사이의 차이
DB CAFE
(→락 정보) |
|||
2번째 줄: | 2번째 줄: | ||
---- | ---- | ||
− | LOCK 걸린 개체 확인 및 LOCK 해제 | + | # LOCK 걸린 개체 확인 및 LOCK 해제 |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
+ | 1. lock 걸린 개체 확인 | ||
− | |||
<source lang="sql"> | <source lang="sql"> | ||
SELECT OBJECT_ID | SELECT OBJECT_ID | ||
23번째 줄: | 14번째 줄: | ||
; | ; | ||
</source> | </source> | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
2. 해당 sid 와 serial 번호로 락걸린 object name 을 확인 | 2. 해당 sid 와 serial 번호로 락걸린 object name 을 확인 | ||
50번째 줄: | 30번째 줄: | ||
</source> | </source> | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
3. sid 와 시리얼 번호로 세션 해제 | 3. sid 와 시리얼 번호로 세션 해제 | ||
68번째 줄: | 35번째 줄: | ||
ALTER SYSTEM KILL SESSION '401, 12761' | ALTER SYSTEM KILL SESSION '401, 12761' | ||
; | ; | ||
− | </source | + | </source |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
4. 락 발생 사용자 및 OBJECT 조회 + 어떤 sql 를 실행중하여 lock 을 걸고 있는지 확인 | 4. 락 발생 사용자 및 OBJECT 조회 + 어떤 sql 를 실행중하여 lock 을 걸고 있는지 확인 | ||
<source lang="sql"> | <source lang="sql"> | ||
108번째 줄: | 59번째 줄: | ||
</source> | </source> | ||
− | + | ## 테이블에 LOCK이 걸렸는지를 보기 | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | # | ||
− | |||
− | |||
− | |||
<source lang="sql"> | <source lang="sql"> | ||
SELECT A.SID | SELECT A.SID | ||
142번째 줄: | 75번째 줄: | ||
WHERE A.SID = B.SID AND B.ID1 = C.OBJECT_ID AND B.TYPE = 'TM' AND C.OBJECT_NAME LIKE UPPER('&테이블명'); </source> | WHERE A.SID = B.SID AND B.ID1 = C.OBJECT_ID AND B.TYPE = 'TM' AND C.OBJECT_NAME LIKE UPPER('&테이블명'); </source> | ||
− | # | + | ## Lock을 잡고있는 세션과 기다리는 세션 조회 |
− | # | ||
− | |||
− | |||
<source lang="sql"> SELECT DECODE(B.LOCKWAIT, NULL, ' ', 'w') AS WW | <source lang="sql"> SELECT DECODE(B.LOCKWAIT, NULL, ' ', 'w') AS WW | ||
− | |||
, B.SID | , B.SID | ||
, B.SERIAL# AS SER# | , B.SERIAL# AS SER# | ||
156번째 줄: | 85번째 줄: | ||
, DECODE(B.COMMAND, 0, NULL, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', B.COMMAND) AS SQLCMD | , DECODE(B.COMMAND, 0, NULL, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', B.COMMAND) AS SQLCMD | ||
, B.PROCESS AS PGM_PSS | , B.PROCESS AS PGM_PSS | ||
− | |||
FROM V$SESSION B | FROM V$SESSION B | ||
− | |||
, (SELECT A.SID, DECODE(B.OWNER, NULL, A.TYPE || '..ing', B.OWNER || '.' || B.OBJECT_NAME) AS OBJECT_NAME | , (SELECT A.SID, DECODE(B.OWNER, NULL, A.TYPE || '..ing', B.OWNER || '.' || B.OBJECT_NAME) AS OBJECT_NAME | ||
FROM V$LOCK A | FROM V$LOCK A | ||
167번째 줄: | 94번째 줄: | ||
WHERE B.SID = A.SID AND B.TADDR IS NOT NULL; </source> | WHERE B.SID = A.SID AND B.TADDR IS NOT NULL; </source> | ||
− | # | + | ## 작업 중인 데이터베이스 트랜잭션 조회 |
− | # | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
185번째 줄: | 105번째 줄: | ||
, T.STATUS | , T.STATUS | ||
, T.START_TIME | , T.START_TIME | ||
− | |||
FROM V$SESSION S | FROM V$SESSION S | ||
− | |||
, V$TRANSACTION T | , V$TRANSACTION T | ||
, DBA_ROLLBACK_SEGS R | , DBA_ROLLBACK_SEGS R | ||
− | |||
WHERE S.TADDR = T.ADDR AND T.XIDUSN = R.SEGMENT_ID; </source> | WHERE S.TADDR = T.ADDR AND T.XIDUSN = R.SEGMENT_ID; </source> | ||
# | # | ||
− | ## | + | ## 열려 있는 커서 조회 |
210번째 줄: | 127번째 줄: | ||
WHERE A.SID = B.SID(+) GROUP BY A.SID, A.OSUSER, A.PROGRAM, A.STATUS; </source> | WHERE A.SID = B.SID(+) GROUP BY A.SID, A.OSUSER, A.PROGRAM, A.STATUS; </source> | ||
− | # | + | ## 잠금 발생 유형 조회 |
− | # | ||
273번째 줄: | 189번째 줄: | ||
</source> | </source> | ||
− | # | + | ## 잠금 상태 오브젝트 조회 |
− | # | ||
295번째 줄: | 210번째 줄: | ||
WHERE A.OBJECT_ID = C.OBJECT_ID AND A.SESSION_ID = B.SID; </source> | WHERE A.OBJECT_ID = C.OBJECT_ID AND A.SESSION_ID = B.SID; </source> | ||
− | # | + | ## 잠금 SQL 구문 조회 |
− | # | ||
302번째 줄: | 216번째 줄: | ||
SELECT B.USERNAME AS USERNAME | SELECT B.USERNAME AS USERNAME | ||
− | |||
, C.SID AS SID | , C.SID AS SID | ||
, C.OWNER AS OBJECT_OWNER | , C.OWNER AS OBJECT_OWNER | ||
309번째 줄: | 222번째 줄: | ||
, A.PIECE | , A.PIECE | ||
, A.SQL_TEXT AS SQL | , A.SQL_TEXT AS SQL | ||
− | |||
FROM V$SQLTEXT A | FROM V$SQLTEXT A | ||
− | |||
, V$SESSION B | , V$SESSION B | ||
, V$ACCESS C | , V$ACCESS C | ||
317번째 줄: | 228번째 줄: | ||
WHERE A.ADDRESS = B.SQL_ADDRESS AND A.HASH_VALUE = B.SQL_HASH_VALUE AND B.SID = C.SID AND C.OWNER!= 'SYS'; </source> | WHERE A.ADDRESS = B.SQL_ADDRESS AND A.HASH_VALUE = B.SQL_HASH_VALUE AND B.SID = C.SID AND C.OWNER!= 'SYS'; </source> | ||
− | # | + | ## 해당 테이블의 세션을 제거하는 쿼리 |
− | # | ||
− | |||
− | |||
<source lang="sql"> /* | <source lang="sql"> /* | ||
329번째 줄: | 237번째 줄: | ||
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' | 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 | <source lang='sql'> SELECT B.BLOCKING_SESSION AS BLOCKING_SESSION_SID |
2018년 11월 9일 (금) 01:57 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
락 정보[편집]
- LOCK 걸린 개체 확인 및 LOCK 해제
1. lock 걸린 개체 확인
SELECT OBJECT_ID
, SESSION_ID -- SID
, ORACLE_USERNAME
, OS_USER_NAME
FROM V$LOCKED_OBJECT
;
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'
;
3. sid 와 시리얼 번호로 세션 해제
ALTER SYSTEM KILL SESSION '401, 12761'
;
</source
4. 락 발생 사용자 및 OBJECT 조회 + 어떤 sql 를 실행중하여 lock 을 걸고 있는지 확인
<source lang="sql">
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
;
- 테이블에 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('&테이블명');
- 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;
- 작업 중인 데이터베이스 트랜잭션 조회
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;
-
- 열려 있는 커서 조회
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);
- 잠금 상태 오브젝트 조회
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;
- 잠금 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';
- 해당 테이블의 세션을 제거하는 쿼리
/*
특정 테이블이 락을 발생하고 있으면 세션을 찾아서 중단시킨다.
*/
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;