Alice's Story

ORA-02049 : 시간초과 : 분산 트랜잭션이 잠금으로 대기중입니다.

위와 같은 메시지가 뜬다면 테이블이 락이 걸려서 TOAD의 사용이 막힌 경우 테이블의 락을 풀어줘야 하는데, 보통은 SYSTEM 계정으로 접근하여 문제가 있는 트랜잭션을 찾는다.

--Transaction 찾는 쿼리
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 ('IFRS_VAL_ACCT') --특정 테이블에서 tx 찾을때 사용
;

찾은후 해당 SID와 SERIAL# 값으로 아래를 실행하여 아래와 같은 쿼리를 실행하면
테이블의 락이 해제된다.

--session kill하는 쿼리  
--alter system kill session 'SID, SERIAL#'
alter system kill session '365,41540';

하지만, 테이블 락을 해제 쿼리를 입력하여도 ORA-00031 : session marked for kill 의 메시지가 나오는 경우가 있다. 직접적으로 아래의 쿼리를 이용하여 oracle 계정으로 시스템에 접근하여 프로세스를 kill 하는 방법이 있다.
( 어떤 세션이 매우 긴 트랜잭션을 수행하다가 강제로 kill이 되게 되면, 우선은 해당 트랜잭션이 사용하던 rollback segment가 회수되게 됩니다. 이때, 그 사이즈가 크면 클 수록 롤백되는 시간이 길어지는데요. 그 동안은 어쨋든 세션은 유지되어야 하는거죠.kill marking만 되구요. 이 때에도 해당 롤백세그먼트는 사용될 수 있습니다. )

SELECT VS.SID, VS.USERNAME, VS.OSUSER,
            VS.PROCESS FG_PID,VP.SPID BG_PID
FROM V$SESSION VS, V$PROCESS VP
WHERE VS.PADDR = VP.ADDR;

위의 쿼리를 실행하여, PID(ProcessID)를 확인후 오라클 계정으로 시스템에 접근하여, kill 명령어를 이용하여 해당 프로세스를 종료한다.

$ kill -9 pid

물론 앞서 메시지가 나오고 방금 kill 이 안되는 경우에도 어느정도 시간을 갖고 기다리면 rollback segment의 수치가 점점 작아져 결국 테이블 락이 해제가 된다.