标签：#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' ; 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) AL