Oracle ORA-00031 오류 원인과 해결 방법 완벽 가이드

ORA-00031
2026년 10월 22일 | DBMS Error 가이드

이 글에서 다루는 내용

ORA-00031 에러의 원인 분석, 해결 SQL, 예방 방법을 실무 관점에서 정리합니다.

ORA-00031 session marked for kill 는?

ORA-00031 에러는 DBA가 특정 세션을 강제 종료하기 위해 ALTER SYSTEM KILL SESSION 명령을 실행했지만, 해당 세션이 즉시 종료되지 않고 “kill 대상”으로 표시(marked)된 상태임을 나타냅니다. 이 에러는 대상 세션이 현재 롤백(rollback) 처리 중이거나 OS 레벨의 리소스를 점유하고 있어 Oracle이 즉시 세션을 제거하지 못할 때 발생합니다. 주로 장시간 실행 중인 트랜잭션, 대용량 DML 작업 또는 분산 트랜잭션(distributed transaction)이 걸려 있는 세션에서 자주 목격되는 현상입니다.

주요 발생 원인

1. 대용량 트랜잭션의 롤백 진행 중

세션이 수백만 건의 DML(INSERT, UPDATE, DELETE)을 처리하는 도중 KILL 명령을 받으면, Oracle은 데이터 일관성을 보장하기 위해 해당 트랜잭션을 롤백해야 합니다. 롤백 작업이 완료되기 전까지 세션은 “marked for kill” 상태로 유지되며, 이 기간 동안 ORA-00031 메시지가 표시됩니다. 롤백 데이터 크기에 따라 수 분에서 수 시간이 걸릴 수 있습니다.

2. OS 프로세스와의 동기화 지연

Oracle 세션은 내부적으로 OS 프로세스(또는 스레드)와 매핑되어 있습니다. KILL SESSION 명령이 Oracle 레벨에서 실행되어도 OS 프로세스가 아직 응답하지 않거나 I/O 대기(wait) 상태에 있을 경우, 세션이 즉시 제거되지 않고 marked for kill 상태로 남습니다. 이 경우 OS 레벨에서 해당 프로세스를 직접 종료해야 완전히 제거됩니다.

3. 분산 트랜잭션(Distributed Transaction) 또는 In-Doubt 트랜잭션

데이터베이스 링크(DB Link)를 통한 분산 트랜잭션이 걸려 있는 세션은 KILL 명령만으로 즉시 종료되지 않습니다. 원격 데이터베이스와의 2PC(Two-Phase Commit) 조율 과정이 남아 있기 때문에, Oracle이 해당 세션을 섣불리 종료하면 분산 트랜잭션의 정합성이 깨질 수 있습니다. 이런 경우 DBA_2PC_PENDING 뷰를 통해 In-Doubt 트랜잭션을 별도로 처리해야 합니다.

해결 방법

1단계: 현재 marked for kill 세션 확인

-- marked for kill 상태의 세션 조회
SELECT s.sid,
       s.serial#,
       s.status,
       s.username,
       s.osuser,
       s.machine,
       s.program,
       s.sql_id,
       p.spid AS os_pid
FROM   v$session s
JOIN   v$process p ON s.paddr = p.addr
WHERE  s.status = 'KILLED';

2단계: KILL SESSION 명령 재실행 (IMMEDIATE 옵션 사용)

일반 KILL SESSION 이후에도 세션이 남아 있다면, IMMEDIATE 옵션을 추가하여 즉시 종료를 시도합니다.

-- 일반 세션 종료
ALTER SYSTEM KILL SESSION 'sid,serial#';

-- 즉시 강제 종료 (권장)
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 예시
ALTER SYSTEM KILL SESSION '145,2381' IMMEDIATE;

3단계: OS 레벨에서 프로세스 강제 종료 (Unix/Linux)

Oracle KILL 명령으로도 세션이 종료되지 않을 경우, OS 프로세스를 직접 제거합니다.

-- 먼저 OS PID(spid) 확인
SELECT p.spid,
       s.sid,
       s.serial#,
       s.username,
       s.status
FROM   v$process p
JOIN   v$session s ON p.addr = s.paddr
WHERE  s.status = 'KILLED';

# Unix/Linux 환경에서 OS 프로세스 강제 종료
kill -9 <spid>

# 예시
kill -9 23541

4단계: 롤백 진행 상황 모니터링

롤백이 진행 중인 경우, 강제로 중단시키기보다 완료를 기다리는 것이 안전합니다.

-- 롤백 진행률 확인 (undo 사용량 기준)
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       t.used_ublk,
       t.used_urec,
       ROUND(t.used_ublk * 8192 / 1024 / 1024, 2) AS undo_mb
FROM   v$session s
JOIN   v$transaction t ON s.taddr = t.addr
WHERE  s.status = 'KILLED';

5단계: 분산 트랜잭션(In-Doubt) 처리

-- In-Doubt 트랜잭션 확인
SELECT local_tran_id,
       global_tran_id,
       state,
       mixed,
       advice,
       tran_comment
FROM   dba_2pc_pending;

-- 수동으로 커밋 또는 롤백 처리 (DBA 판단 필요)
COMMIT FORCE 'local_tran_id';
-- 또는
ROLLBACK FORCE 'local_tran_id';

예방 방법

1. 대용량 DML은 배치(Batch) 단위로 분할 처리

한 번의 트랜잭션에서 수백만 건의 DML을 처리하면 롤백 세그먼트가 커지고, 세션 종료 시 막대한 롤백 시간이 소요됩니다. 아래와 같이 BULK COLLECT와 FORALL, 또는 ROWNUM 기반 분할 처리를 통해 트랜잭션 단위를 작게 유지하면 세션 종료 시 롤백 부담을 최소화할 수 있습니다.

-- 10,000건씩 나누어 처리하는 예시
BEGIN
  LOOP
    DELETE FROM large_table
    WHERE  status = 'OLD'
    AND    ROWNUM <= 10000;

    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
  END LOOP;
END;
/

2. 세션 및 리소스 모니터링 자동화

장시간 실행 중인 쿼리나 Lock 대기 세션을 사전에 감지하고 자동으로 알림을 발송하는 모니터링 체계를 구축해야 합니다. Oracle Enterprise Manager(OEM)의 알림 기능이나 사용자 정의 모니터링 스크립트를 주기적으로 실행하여 문제 세션을 조기에 발견하고, PROFILE의 IDLE_TIME 파라미터를 설정해 유휴 세션이 자동으로 종료되도록 관리합니다.

-- 장시간 실행 쿼리 모니터링 (10분 이상)
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       ROUND(q.elapsed_time / 1000000, 1) AS elapsed_sec,
       q.sql_text
FROM   v$session s
JOIN   v$sql q ON s.sql_id = q.sql_id
WHERE  q.elapsed_time > 600 * 1000000
ORDER  BY q.elapsed_time DESC;

-- PROFILE을 통한 IDLE_TIME 설정 (단위: 분)
CREATE PROFILE limited_profile LIMIT
  IDLE_TIME    30
  CONNECT_TIME 480;

ALTER USER app_user PROFILE limited_profile;