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';
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';
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';
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;
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;
# Linux/Unix: hard kill using SPID from above query
kill -9
⚠️ 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;
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;
/
Related Errors
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.
More...
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';
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';
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';
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;
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;
# Linux/Unix: hard kill using SPID from above query
kill -9
⚠️ 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;
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;
/
Related Errors
| 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.
More...