오라클 세션락 총정리
1.락 걸린 테이블 확인하는 쿼리
SELECT
dbo.object_name
,dbo.owner
,dbo.object_type
,vlo.xidusn
,vlo.session_id
,vlo.locked_mode
FROM V$LOCKED_OBJECT vlo, DBA_OBJECTS dbo
WHERE vlo.object_id = dbo.object_id;
2.특정 테이블이 락 걸렸는지 확인하는 쿼리
SELECT
vs.sid
,vs.serial#
,vl.type
,dbo.object_name
FROM V$SESSION vs, V$LOCK vl, DBA_OBJECTS dbo
WHERE vs.sid = vl.sid
AND vl.id1 = dbo.object_id
AND vl.type = 'TM'
AND dbo.object_name IN ('테이블명');
3. 락을 발생한 사용자와 실행 SQL, OBJECT 조회
SELECT DISTINCT
x.session_id
,a.serial#
,d.object_name
,a.machine
,a.terminal
,a.program
,b.address
,b.piece
,b.sql_text
FROM V$LOCKED_OBJECT x, V$SESSION a, V$SQLTEXT b, DBA_OBJECTS d
WHERE x.session_id = a.sid
AND x.object_id = d.object_id
AND a.sql_address = b.address
ORDER BY b.address, b.piece;
4. 락 걸린 사용자 제거(kill) 실행문 추출 쿼리
SELECT DISTINCT
'ALTER SYSTEM KILL SESSION ''' || a.sid ||', ' || a.serial# || ''';'
FROM gv$locked_object x, gv$session a, dba_objects d
WHERE x.session_id = a.sid and x.object_id = d.object_id
;
ALTER SYSTEM KILL SESSION '26, 6044';
==========================================================================
-- 락걸린 테이블 확인
SELECT DO.OBJECT_NAME,
DO.OWNER,
DO.OBJECT_TYPE,
DO.OWNER,
VO.XIDUSN,
VO.SESSION_ID,
VO.LOCKED_MODE
FROM V$LOCKED_OBJECT VO, DBA_OBJECTS DO
WHERE VO.OBJECT_ID = DO.OBJECT_ID;
-- 해당 테이블에 LOCK 이 걸렸는지.
SELECT A.SID,
A.SERIAL#,
B.TYPE,
C.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 IN ('테이블명');
-- 락발생 사용자와 SQL, OBJECT 조회
SELECT DISTINCT X.SESSION_ID,
A.SERIAL#,
D.OBJECT_NAME,
A.MACHINE,
A.TERMINAL,
A.PROGRAM,
B.ADDRESS,
B.PIECE,
B.SQL_TEXT
FROM V$LOCKED_OBJECT X,
V$SESSION A,
V$SQLTEXT B,
DBA_OBJECTS D
WHERE X.SESSION_ID = A.SID AND X.OBJECT_ID = D.OBJECT_ID AND A.SQL_ADDRESS = B.ADDRESS
ORDER BY B.ADDRESS, B.PIECE;
-- 현재 접속자의 SQL 분석
SELECT DISTINCT A.SID,
A.SERIAL#,
A.MACHINE,
A.TERMINAL,
A.PROGRAM,
B.ADDRESS,
B.PIECE,
B.SQL_TEXT
FROM V$SESSION A, V$SQLTEXT B
WHERE A.SQL_ADDRESS = B.ADDRESS
ORDER BY A.SID,
A.SERIAL#,
B.ADDRESS,
B.PIECE;
-- 락 세션 죽이기
SELECT A.SID, A.SERIAL#
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 = '테이블명';
ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';
-- 락 세션 죽이는 sql 문
SELECT DISTINCT X.SESSION_ID,
A.SERIAL#,
D.OBJECT_NAME,
A.MACHINE,
A.TERMINAL,
A.PROGRAM,
A.LOGON_TIME,
'ALTER SYSTEM KILL SESSION''' || A.SID || ', ' || A.SERIAL# || ''';'
FROM GV$LOCKED_OBJECT X, GV$SESSION A, DBA_OBJECTS D
WHERE X.SESSION_ID = A.SID AND X.OBJECT_ID = D.OBJECT_ID
ORDER BY LOGON_TIME;
============================================================================================
<Lock걸린 부분이 있는지 확인>
SELECT C.OBJECT_NAME
, B.SID
, B.SERIAL#
, 'ALTER SYSTEM KILL SESSION '''||B.SID||','||B.SERIAL#||''';'
FROM V$LOCKED_OBJECT A
JOIN V$SESSION B ON ( A.SESSION_ID = B.SID )
JOIN DBA_OBJECTS C ON ( A.OBJECT_ID = C.OBJECT_ID );
위 쿼리로 세션을 Kill
<만약 Lock 내역이 보이지 않는다면>
SELECT A.LOCK_ID1
, A.SESSION_ID
, 'ALTER SYSTEM KILL SESSION '''||B.SID||','||B.SERIAL#||''';'
FROM DBA_LOCK_INTERNAL A
JOIN V$SESSION B ON ( A.SESSION_ID = B.SID )
WHERE LOCK_ID1 LIKE '%object_name%'; --> Object Name 입력
- Lock이 걸린 것으로 의심되는 object의 이름을 넣어 위 쿼리로 확인
- 내역이 보이면 세션을 Kill 시킨다.
<하지만 여전히 세션 내역에 존재하고>
SELECT STATUS
FROM V$SESSION
WHERE SID = 'session_id'; --> Session ID 입력
위 쿼리의 결과(STATUS)가 'Killed'라고 나온다면
<서버에서 돌고있는 Lock 관련 프로세스 확인>
SELECT B.SPID
, 'KILL -9 ' || B.SPID
FROM V$SESSION A
JOIN V$PROCESS B ON ( A.PADDR = B.ADDR )
WHERE A.SID = 'session_id'; --> Session ID 입력
<프로세스 ID가 실제 존재하는지 서버에서 확인>
ps -ef | grep 프로세스ID
<서버 프로세스 kill>
#DB서버
kill -9 프로세스ID
Kill 후 쿼리로 세션 존재여부 확인