DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00031 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00031: Session Marked for Kill — What It Means and How to Fix It

ORA-00031 occurs when a DBA issues ALTER SYSTEM KILL SESSION but Oracle cannot terminate the target session immediately. Instead, Oracle marks the session as "KILLED" and waits for it to reach a safe termination point — typically after completing a rollback or releasing OS-level resources. This is less of a hard error and more of a transitional state that every Oracle DBA will eventually encounter.

Top 3 Causes

1. Large Transaction Rollback in Progress

When you kill a session mid-transaction, Oracle must roll back all uncommitted changes to preserve data integrity. The larger the transaction, the longer the session stays in KILLED status.

-- Check rollback progress for KILLED sessions
SELECT s.sid,
       s.serial#,
       s.username,
       t.used_ublk AS undo_blocks,
       t.used_urec AS undo_records
FROM   v$session s
JOIN   v$transaction t ON s.taddr = t.addr
WHERE  s.status = 'KILLED';
Enter fullscreen mode Exit fullscreen mode

2. Unresponsive or Disconnected Client

If the client network connection is broken or the client process has hung, Oracle cannot deliver the kill signal. The session lingers in KILLED state until the OS-level connection finally times out.

-- Find the OS process ID (SPID) for stuck KILLED sessions
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       p.spid AS os_pid,
       s.machine,
       s.program
FROM   v$session s
JOIN   v$process p ON s.paddr = p.addr
WHERE  s.status = 'KILLED';
Enter fullscreen mode Exit fullscreen mode

3. OS-Level I/O or Resource Wait

Sessions blocked at the OS level (disk I/O stall, memory pressure, storage issues) cannot respond to Oracle's internal kill signal. In these cases, only an OS-level process termination will resolve the problem.

-- Identify what the session was waiting on before being killed
SELECT sid,
       serial#,
       status,
       event,
       wait_class,
       seconds_in_wait
FROM   v$session
WHERE  status = 'KILLED';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Option 1 — Use the IMMEDIATE keyword (recommended first step)

-- Standard kill (asynchronous)
ALTER SYSTEM KILL SESSION '123,456';

-- Immediate kill (forces faster termination)
ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;
Enter fullscreen mode Exit fullscreen mode

Option 2 — OS-level kill (last resort)

-- Get the SPID first
SELECT p.spid
FROM   v$session s
JOIN   v$process p ON s.paddr = p.addr
WHERE  s.sid     = 123
AND    s.serial# = 456;
Enter fullscreen mode Exit fullscreen mode 
# Linux/Unix: hard kill using SPID from above query
kill -9 <spid>
Enter fullscreen mode Exit fullscreen mode

⚠️ Warning: Use OS-level kill -9 only after confirming no active rollback is in progress. Interrupting a rollback at the OS level can lead to block corruption.

Prevention Tips

1. Set IDLE_TIME in user profiles to automatically disconnect sessions that have been inactive too long — reducing the need for manual kills in the first place.

-- Create a profile that disconnects idle sessions after 30 minutes
CREATE PROFILE app_profile LIMIT
    IDLE_TIME    30
    CONNECT_TIME 480;

ALTER USER app_user PROFILE app_profile;
Enter fullscreen mode Exit fullscreen mode

2. Use batch commits for large DML operations to minimize rollback size, so that if a session must be killed, the rollback completes quickly and ORA-00031 resolves faster.

-- Batch delete with intermediate commits
BEGIN
    LOOP
        DELETE FROM large_table
        WHERE  status = 'EXPIRED'
        AND    ROWNUM <= 5000;

        EXIT WHEN SQL%ROWCOUNT = 0;
        COMMIT;
    END LOOP;
END;
/
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-00028 Session successfully killed — seen by the killed session's user
ORA-00030 No such session — invalid SID/Serial# combination used in kill command
ORA-01013 User requested cancel of current operation

Pro Tip: Before reaching for kill -9, always check v$transaction to see if a rollback is actively running. Patience is often the safest fix — let Oracle finish the rollback cleanly rather than risk data block corruption with a forced OS kill.

Top comments (0)