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

오라클 락 조회 쿼리

 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
;

출력예)

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

컬럼설명)

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

락 모드에 따른 값)

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

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

 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

   ;

출력예)

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

컬럼설명)

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

세션 해제

ALTER SYSTEM KILL SESSION 'sid,serial#

(commit 불필요)

사용예)

 ALTER SYSTEM KILL SESSION '401, 12761';

SESSION_ID와 SERIAL#이 있는 이유

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

case의 예)

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# 은 달라짐