오라클 테이블 lock 현상 해결 방안(SESSION KILL)

- ORACLE 11g 기준

1. oracle 접속 및 Lock 걸린 Table 조회

1) lock 걸린 oracle user명을 알 경우

  select * from v$locked_object where oracle_username = 'oracle user name'; 

 2) lock 걸린 oracle user명을 모를 경우 

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;

위 과정을 통하여 user 명과  oracle locked_object를 알 수 있다. 

2. lock 걸린 session에 대한 kill 명령(TABLE 명을 이용한 SID, SERIAL 값 추출)

1. 의 결과를 알게 된 테이블 명을 아래의 SQL문에 입력하여 SID, SERIAL 값을 알아낸다. 

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 = '테이블 명';

3. SESSION KILL  진행 

알게된 SID, SERIAL 값을 통하여 SESSION KILL 진행 

alter system kill session 'sid,serial' immediate

--> session kill 수행 완료 

TIP) 위의 작업을 좀 더 편하게 하려면 

select 'alter system kill session '''||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 'sid,serial' 의 값이 출력되고 

이를 바로 실행창에 넣어 실행하면 아주 편하게 session kill을 할 수 있음.