ORA-00031: Session Marked for Kill - Force Terminate Zombie Sessions

ORA-00031: Session Marked for Kill

Error Overview

Error Text: ORA-00031: session marked for kill

The ORA-00031 error occurs when a DBA issues ALTER SYSTEM KILL SESSION against a session, but the session cannot be terminated immediately. Oracle marks the session for kill and returns this error to indicate the kill request has been acknowledged — but the session is still alive. The session will be cleaned up by PMON (Process Monitor) the next time it attempts a database call, or by Oracle’s internal cleanup mechanisms.

This is one of the most frustrating situations for DBAs: a runaway query, a blocking session, or a zombie connection refuses to die even after an explicit kill command. Understanding why sessions resist termination and what options are available is essential for any production DBA.

Common Causes

1. Session Is Waiting on a Slow OS Operation

• The session is performing a large I/O operation (e.g., a full table scan writing to a sort area)
• Network I/O in progress — the session is waiting for a response from a database link or remote service
• The OS is waiting on a disk write that cannot be interrupted mid-operation

2. Session Is in the Middle of a Transaction Rollback

• The session had a large, long-running transaction when the kill was issued
• Oracle must roll back all uncommitted changes before the process can be freed
• Rollback time is proportional to the transaction size — very large transactions can take as long to roll back as they did to execute

3. Distributed Transaction Involvement

• The session is a participant in a distributed transaction across a database link
• Oracle must coordinate with the remote database before terminating the local session
• Two-phase commit (2PC) in progress prevents immediate cleanup

4. RAC (Real Application Clusters) Considerations

• The session physically exists on a different RAC node than where the kill was issued
• Cross-instance kill requires inter-node communication and takes longer
• The @instance_number qualifier was not used and Oracle is killing the wrong instance

5. Operating System Process Not Responding

• The server process (shadow process) is stuck in an uninterruptible OS wait
• The OS process is in a kernel wait state (e.g., D state on Linux) and cannot receive signals
• A bug or hardware issue is causing the process to be unkillable through normal means

6. PMON Backlog

• PMON is overloaded with cleanup tasks from many recently killed or disconnected sessions
• The marked-for-kill session is queued behind other cleanup work
• PMON itself is experiencing performance degradation

Diagnostic Queries

Identify the Marked Session

-- Find sessions marked for kill
SELECT
  sid,
  serial#,
  username,
  status,
  last_call_et,
  blocking_session,
  event,
  wait_class,
  seconds_in_wait,
  program,
  machine,
  osuser,
  process      AS client_pid,
  sql_id
FROM v$session
WHERE status = 'KILLED'
ORDER BY last_call_et DESC;

-- Find the corresponding server process OS PID
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  p.spid        AS server_os_pid,
  p.pid         AS oracle_pid,
  s.last_call_et,
  s.program,
  s.machine
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.status = 'KILLED'
ORDER BY s.last_call_et DESC;

Check What the Session Was Doing

-- Identify the SQL the killed session was running
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.sql_id,
  q.sql_text,
  q.elapsed_time / 1000000 AS elapsed_seconds,
  q.executions
FROM v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'KILLED';

-- Check for active rollback (undo generation)
SELECT
  s.sid,
  s.serial#,
  s.username,
  t.used_ublk      AS undo_blocks_used,
  t.used_urec      AS undo_records_used,
  t.start_time     AS transaction_start,
  s.last_call_et   AS seconds_since_last_call
FROM v$session s
JOIN v$transaction t ON s.taddr = t.addr
WHERE s.status = 'KILLED'
ORDER BY t.used_ublk DESC;

Check for Blocking Locks Held by the Killed Session

-- Check if the killed session is still holding locks
SELECT
  l.sid,
  l.type,
  l.lmode,
  l.request,
  l.id1,
  l.id2,
  o.owner,
  o.object_name,
  o.object_type
FROM v$lock l
LEFT JOIN dba_objects o ON l.id1 = o.object_id
WHERE l.sid IN (
  SELECT sid FROM v$session WHERE status = 'KILLED'
)
ORDER BY l.sid;

-- Check blocking chains involving killed sessions
SELECT
  blocker.sid          AS blocking_sid,
  blocker.serial#      AS blocking_serial,
  blocker.status       AS blocking_status,
  blocker.username     AS blocking_user,
  waiter.sid           AS waiting_sid,
  waiter.username      AS waiting_user,
  waiter.event         AS waiting_on
FROM v$session blocker
JOIN v$session waiter ON waiter.blocking_session = blocker.sid
WHERE blocker.status = 'KILLED'
ORDER BY blocker.sid;

Estimate Rollback Completion Time

-- Estimate how long rollback will take
SELECT
  s.sid,
  s.serial#,
  t.used_ublk                                         AS undo_blocks_remaining,
  t.used_urec                                         AS undo_records_remaining,
  ROUND(t.used_ublk * 8192 / 1024 / 1024, 2)         AS undo_mb_remaining,
  TO_CHAR(t.start_scn)                               AS start_scn,
  (SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 24 * 60 AS minutes_running
FROM v$session s
JOIN v$transaction t ON s.taddr = t.addr
WHERE s.status = 'KILLED';

Check PMON Activity

-- Check PMON process status
SELECT
  p.pid,
  p.spid     AS os_pid,
  p.program,
  p.background,
  s.last_call_et
FROM v$process p
LEFT JOIN v$session s ON p.addr = s.paddr
WHERE p.background = 1
  AND p.program LIKE '%PMON%';

-- Check for recent session cleanup events in the alert log
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSDATE - 1/24
  AND (message_text LIKE '%PMON%'
       OR message_text LIKE '%dead%'
       OR message_text LIKE '%cleanup%')
ORDER BY originating_timestamp DESC;

Step-by-Step Resolution

1. Wait and Monitor (First Step — Always)

Before taking drastic action, determine whether the session is actively rolling back:

-- Poll the session every 60 seconds to track rollback progress
-- Run this multiple times and compare undo_blocks_remaining
SELECT
  s.sid,
  s.serial#,
  s.status,
  t.used_ublk   AS undo_blocks_remaining,
  t.used_urec   AS undo_records_remaining
FROM v$session s
LEFT JOIN v$transaction t ON s.taddr = t.addr
WHERE s.status = 'KILLED';

If undo_blocks_remaining is decreasing between polls, the session is rolling back normally. Wait for it to complete — interrupting rollback can lead to data corruption.

2. Re-Issue the Kill with IMMEDIATE Clause

If the original kill was issued without IMMEDIATE, try again with the flag:

-- Re-issue kill with IMMEDIATE to force faster cleanup
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- Example:
ALTER SYSTEM KILL SESSION '145,23456' IMMEDIATE;

The IMMEDIATE clause instructs Oracle to release resources and disconnect the session without waiting for the session to acknowledge the kill request. This is generally safe for sessions not in the middle of a transaction.

3. Kill at the OS Level

If Oracle cannot terminate the session through normal means, kill the underlying OS server process:

-- Step 1: Get the OS process ID of the server process
SELECT
  s.sid,
  s.serial#,
  s.username,
  p.spid   AS server_os_pid
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid = &target_sid
  AND s.serial# = &target_serial;

# Step 2: On Linux/Unix, kill the process with SIGTERM first
kill -15 <spid>

# If SIGTERM does not work after 30 seconds, use SIGKILL
kill -9 <spid>

# On Windows, use taskkill
taskkill /F /PID <spid>

After an OS-level kill, PMON will detect the dead process and perform cleanup. The session entry will disappear from v$session once PMON finishes.

4. RAC-Specific Kill

In a RAC environment, the session may exist on a different instance:

-- Identify which instance the session exists on
SELECT
  inst_id,
  sid,
  serial#,
  username,
  status,
  machine
FROM gv$session
WHERE status IN ('ACTIVE', 'KILLED', 'INACTIVE')
  AND username IS NOT NULL
ORDER BY inst_id, sid;

-- Kill a session on a specific instance
ALTER SYSTEM KILL SESSION 'sid,serial#,@instance_number' IMMEDIATE;

-- Example: Kill session on instance 2
ALTER SYSTEM KILL SESSION '87,14433,@2' IMMEDIATE;

5. Disconnect Rather Than Kill (Non-SYSDBA Sessions)

As an alternative to killing, use DISCONNECT SESSION to gracefully disconnect without waiting for a kill acknowledgment:

-- Disconnect immediately (similar to OS-level disconnect)
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

-- Post-transaction disconnect (waits for current transaction to complete)
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

DISCONNECT SESSION IMMEDIATE terminates the server process directly without waiting for PMON cleanup, and is often faster than KILL SESSION with IMMEDIATE.

6. Verify Cleanup Completed

After the kill or disconnect:

-- Confirm the session is gone from v$session
SELECT sid, serial#, username, status
FROM v$session
WHERE sid = &target_sid;

-- Confirm locks are released
SELECT sid, type, lmode, id1, id2
FROM v$lock
WHERE sid = &target_sid;

-- Check for orphaned transactions
SELECT
  xidusn, xidslot, xidsqn,
  status, start_time, used_ublk
FROM v$transaction
WHERE ses_addr NOT IN (SELECT saddr FROM v$session);

Prevention Strategies

1. Set Connection Timeouts to Avoid Zombie Sessions

-- Create a profile with idle timeout
CREATE PROFILE app_user_profile LIMIT
  IDLE_TIME          30      -- 30 minutes idle
  CONNECT_TIME       480     -- 8 hours max connection time
  SESSIONS_PER_USER  5;

-- Assign the profile to application users
ALTER USER app_user PROFILE app_user_profile;

-- Verify profile assignments
SELECT username, profile
FROM dba_users
WHERE profile != 'DEFAULT'
ORDER BY username;

2. Configure Dead Connection Detection

-- Set SQLNET.EXPIRE_TIME in sqlnet.ora to detect dead connections
-- Add to $ORACLE_HOME/network/admin/sqlnet.ora:
-- SQLNET.EXPIRE_TIME = 10  (minutes)

-- Verify via parameter
SELECT name, value
FROM v$parameter
WHERE name LIKE '%expire%'
   OR name LIKE '%dead%';

3. Monitor for Long-Running Sessions Proactively

-- Create a scheduler job to alert on sessions running > 2 hours
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'ALERT_LONG_RUNNING_SESSIONS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      DECLARE
        v_count NUMBER;
      BEGIN
        SELECT COUNT(*) INTO v_count
        FROM v$session
        WHERE status = ''ACTIVE''
          AND last_call_et > 7200
          AND username IS NOT NULL;

        IF v_count > 0 THEN
          -- Insert into an alerts table or send DBMS_ALERT
          INSERT INTO dba_monitoring_alerts
            (alert_time, alert_type, detail)
          VALUES
            (SYSDATE, ''LONG_SESSION'',
             v_count || '' sessions running > 2 hours'');
          COMMIT;
        END IF;
      END;
    ]',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',
    enabled         => TRUE
  );
END;
/

4. Best Practices for Session Management

• Always try ALTER SYSTEM KILL SESSION ... IMMEDIATE before resorting to OS-level kills
• In RAC environments, always specify the @instance_number qualifier to ensure the correct instance is targeted
• Never kill a session that is actively rolling back a large transaction — wait for rollback to complete
• Use connection pools with proper timeout configurations to reduce the occurrence of zombie sessions
• Document any OS-level process kills in change management records for audit purposes

Diagnostic Scripts

These Oracle Day by Day scripts can help manage and diagnose session issues:

• gvsess.sql — Comprehensive session analysis across all instances
• health.sql — Database health check including session and lock analysis

Related Errors

• ORA-00028 - Your session has been killed (received by the killed session)
• ORA-00054 - Resource busy and acquire with NOWAIT specified
• ORA-00060 - Deadlock detected while waiting for resource
• ORA-01013 - User requested cancel of current operation

Emergency Response

Quick Fixes

1. Re-issue kill with IMMEDIATE

   ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

2. Use DISCONNECT SESSION as alternative

   ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

3. OS-level process kill as last resort

   -- Get OS PID first
   SELECT p.spid FROM v$process p JOIN v$session s ON p.addr = s.paddr
   WHERE s.sid = &sid AND s.serial# = &serial;
   -- Then: kill -9 <spid> from OS prompt

Post-Resolution Cleanup

-- Confirm session is fully removed
SELECT sid, serial#, username, status
FROM v$session
WHERE status = 'KILLED';

-- Check for orphaned locks
SELECT sid, type, lmode, id1, id2
FROM v$lock l
WHERE sid NOT IN (SELECT sid FROM v$session);

-- Check for orphaned transactions that need cleanup
SELECT t.xidusn, t.used_ublk, t.status
FROM v$transaction t
WHERE t.ses_addr NOT IN (SELECT saddr FROM v$session);

-- Review alert log for PMON cleanup messages
SELECT originating_timestamp, message_text
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSDATE - 1/24
  AND message_text LIKE '%dead%process%'
ORDER BY originating_timestamp DESC;