0%

오라클 락 조회, 오라클 테이블 락 확인 쿼리, 오라클 락 해제

oracle

오라클 락 조회 쿼리

1
2
3
4
5
6
7
8
 SELECT L.OBJECT_ID, L.SESSION_ID, L.ORACLE_USERNAME, L.OS_USER_NAME, L.LOCKED_MODE, D.OBJECT_NAME
FROM V$LOCKED_OBJECT L,
DBA_OBJECTS D
WHERE
1=1
AND D.OBJECT_ID = L.OBJECT_ID
;

출력예)

1
2
3
4

| OBJECT_ID | SESSION_ID | ORACLE_USERNAME | OS_USER_NAME | LOCKED_MODE | OBJECT_NAME |
| --- | --- | --- | --- | --- | --- |
| 64974859 | 3828 | ora_user | username | 3 | table1 |

컬럼설명)

1
2
3
4
| OBJECT_ID | SESSION_ID | ORACLE_USERNAME | OS_USER_NAME | LOCKED_MODE | OBJECT_NAME |
| --- | --- | --- | --- | --- | --- |
| 오브젝트 고유아이디 | 세션 아이디 | 로그인한 오라클 계정 | 쿼리 실행한 머신에서의 username | 락 모드 넘버 (아래 참고) | lock 걸린 object이름 |

락 모드에 따른 값)

1
2
3
4
5
6
1, 'Null'
2, 'RS' -- Row-Shared
3, 'RX' -- Row-Exclusive
4, 'S' -- Shared
5, 'SRX' -- Shared-Row-Exclusive
6, 'X' -- Exclusive

오라클 락 걸린 쿼리 조회하기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT DISTINCT T1.SESSION_ID
, T2.SERIAL#
, T4.OBJECT_NAME
, T2.MACHINE
, T2.PROGRAM
, 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

;

출력예)

1
2
3
| SESSION_ID | SERIAL# | OBJECT_NAME | MACHINE |  PROGRAM |SQL_TEXT | 
| --- | --- | --- | --- | --- | --- |
| 10402 | 61053 | table1 | machine1 | java@machine1 (TNS V1-V3) | insert into aaa... |

컬럼설명)

1
2
3
4
| SESSION_ID | SERIAL# | OBJECT_NAME | MACHINE |  PROGRAM |SQL_TEXT | 
| --- | --- | --- | --- | --- | --- |
| 세션고유식별자 | 세션을 세부적으로 구분하기위한 값 | object명 | 해당DB로 접속한 머신 | 쿼리수행중인 원격지의 프로그램 | 락을 유발하는 쿼리 |

세션 해제

1
ALTER SYSTEM KILL SESSION 'sid,serial#

(commit 불필요)

사용예)

1
ALTER SYSTEM KILL SESSION '401, 12761';

SESSION_ID와 SERIAL#이 있는 이유

오라클에서 SESSION_ID외에 고유 구분자로 SERIAL#가 있는 이유는, session은 재활용되기 때문이라고 볼수 있습니다.
예를 들어, 하나의 특정 세션을 닫고 새로운 세션을 열 경우,
최악의 경우 동일한 세션이 다음에 오는 세션에 할당될 가능성이 있습니다.
특정 사용자를 구분하거나, 연결 kill 등을 하기위해 session id외에 값이 필요하여 serial number가 필요하게 되었습니다.

case의 예)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> select sid, serial# from v$session where program like '%Pl%';

SID SERIAL#
---------- ----------
139 37758
142 41671

SQL> select sid, serial# from v$session where program like '%Pl%';
select sid, serial# from v$session where program like '%Pl%'

Not logged on <---------- 여기서 로그 오프
SQL> select sid, serial# from v$session where program like '%Pl%';
SID SERIAL#
---------- ----------
139 37768
142 41683 <----------- 오라클에서 동일한 sid를 부여했지만, SERIAL# 은 달라짐