ORA-00031: session marked for kill

해결 방법 : 정상적인 Rollback 처리를 위해서 기다리는 걸 권장함

Lock이 해제 되지 않는 경우 처리 되는 사항

테이블이 Lock이 걸려서 특정 Row 또는 테이블을 수정할 수 없는 경우 일반적으로 

 

Alter system kill session 'SID_no, SERIAL_no' ;

 

명령을 날려서 강제로 종료 하는 경우가 많음

 

그런데 이렇게 해도 해제가 되지 않고 오류메세지와 함께

'ORA-00031 : session marked for kill ' 이라는 메세지를 띄워주고 종료되어버림

 

물론 기다리면 해제되는 경우가 일반적이고 정상적인 Rollback 처리를 위해서는 기다리는 걸 권함

아래 rollback 중인 내용을 확인하는 방법

 

Undo 현재 사용량 확인

SQL>

col SID_SERIAL for a10

col ORAUSER for a10

col PROGRAM for a30

col UNDOSEG for a30

col undo for a10

SELECT TO_CHAR(s.sid)||',' ||TO_CHAR(s.serial#) sid_serial,

         NVL(s.username, 'None') orauser,

         s.program,

         r.name undoseg,

         t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"

FROM   sys.v_$rollname    r,

        sys.v_$session     s,

        sys.v_$transaction t,

        sys.v_$parameter   x

WHERE s.taddr = t.addr

AND r.usn   = t.xidusn(+)

AND x.name  = 'db_block_size'

/

 

SID_SERIAL ORAUSER    PROGRAM                             UNDOSEG                Undo

---------- ---------- ------------------------------ ------------------------------ ----------

46,1127    JSH          sqlplus@prsjedb2 (TNS V1-V3)   _SYSSMU14_2585002358$        65552K

 

현재 Rollback 진행률 확인

SQL>

col OPNAME for a10

col TARGET for a20 

col SOFAR for 999999999

col UNITS for a10

select OPNAME,TARGET,SOFAR,TOTALWORK,UNITS,START_TIME,TIME_REMAINING, ELAPSED_SECONDS 

from v$session_longops where username='JSH'

/

 

OPNAME       TARGET             SOFAR  TOTALWORK UNITS     START_TIM TIME_REMAINING ELAPSED_SECONDS

---------- -------------------- ---------- ---------- ---------- --------- -------------- ---------------

Table Scan JSH.MAXTEST             25397    25397 Blocks     02-JAN-19        0           36

Table Scan JSH.MAXTEST             25397    25397 Blocks     02-JAN-19        0           67

 

Oracle Database 10g에서는 transaction rollback이 수행될때 , 진행중인 transaction이 

$SESSION_LONGOPS view에 event가 기록되어짐

이 view는 10g 이전 데이타베이스에도 존재하였지만, transaction을 rollback을 하는것은 

기록하지 못했었음. 

Rollback의 경우에 , 6초보다 길어지는 경우에 이 view에 기록되어짐

 

$SESSION_LONGOPS view는 모든 진행중인 작업에 대해 보여주는데, "Transaction Rollback" 

이라고 기록되어진 OPNAME 컬럼을 통해 확인할수 있다.

 

각 컬럼 설명

 

TIME_REMAINING : 예상된 남겨진 시간을 보여준다. (초단위) 

ELAPSED_SECONDS : 이제까지 수행한 시간을 보여준다. (초단위) 

TOTALWORK : 수행하는 작업의 전체 양을 보여준다. 

SOFAR : 이제까지 수행한 작업의 양을 보여준다. 

UNITS : 작업한 양의 단위를 보여준다.

LAST_UPDATE_TIME : view의 내용이 현재것인지를 판단하게 한다.

 

그러나 어쩔 수 없이 강제 해제를 시켜야 한다면 다음과 같이 처리 하면 됨

 

# 리눅스/유닉스 계열

SQL>

SELECT

X.SID,

X.USERNAME,

X.OSUSER,

X.PROCESS AS FG_PID,

Y.SPID BG_PID

FROM V$SESSION X, V$PROCESS Y

WHERE X.PADDR = Y.ADDR 

AND X.USERNAME IS NOT NULL

/

 

       SID USERNAME              OSUSER             FG_PID           BG_PID

---------- ------------------------------ ---------------------------------

       27 SYS                  oracle             6124              18598

       48 JSH                  oracle             5139              11271

       45 SYS                  oracle             6124              9601

       46 JSH                  oracle             11796              11809

 

해당 쿼리를 SYSTEM 계정에서 실행한 뒤 PID(PROCESSID)를 확인 후 시스템 접근 후 해당 프로세스를 종료 하면 됨

 

$ KILL -9 PID

 

원인 : kill 명령 후 트랜젝션 rollback 중이기 때문에 바로 kill 되지 않은 것

dml에 의해 lock 가 걸리고 해당 세션이 끊겼을때 rollback 중인 내용이 많을 때 발생

기다리면 해제되는 경우가 일반적이고 정상적인 Rollback 처리를 위해서는 기다리는 걸 권함