How to Kill an Oracle Session - SID, Serial, OS PID

How to Kill an Oracle Session

Sometimes you need to forcibly terminate an Oracle session — a runaway query consuming all CPU, a blocking lock that’s halting other users, or a zombie session that won’t disconnect cleanly. Oracle provides KILL SESSION (graceful) and DISCONNECT SESSION (immediate) for this purpose. This guide covers both, plus OS-level termination for stubborn sessions.

Prerequisites

You need the ALTER SYSTEM privilege to kill sessions. DBAs typically have this. You cannot kill SYS background process sessions.

-- Verify you have ALTER SYSTEM privilege
SELECT privilege FROM session_privs WHERE privilege = 'ALTER SYSTEM';

Step 1: Find the Session to Kill

You need the SID and SERIAL# to identify the session uniquely. SERIAL# changes each time a session reuses a SID slot, preventing accidental termination of the wrong session.

-- Find sessions by username
SELECT sid, serial#, username, status, osuser, machine,
       program, module, logon_time, last_call_et AS idle_seconds
FROM v$session
WHERE username = 'APP_USER'
  AND username IS NOT NULL
ORDER BY last_call_et DESC;

-- Find blocking sessions (sessions causing lock waits)
SELECT
  w.sid    AS waiting_sid,
  w.serial# AS waiting_serial,
  w.username AS waiting_user,
  b.sid    AS blocking_sid,
  b.serial# AS blocking_serial,
  b.username AS blocking_user,
  b.status  AS blocking_status,
  b.osuser  AS os_user,
  b.machine AS machine,
  b.logon_time,
  b.last_call_et AS blocking_idle_secs
FROM v$session w
JOIN v$session b ON w.blocking_session = b.sid
WHERE w.blocking_session IS NOT NULL
ORDER BY b.last_call_et DESC;

-- Find long-running queries (running > 10 minutes)
SELECT s.sid, s.serial#, s.username, s.status,
       ROUND(q.elapsed_time/1000000, 0) AS elapsed_sec,
       ROUND(q.cpu_time/1000000, 0)     AS cpu_sec,
       SUBSTR(q.sql_text, 1, 100)        AS sql_preview
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id AND s.sql_child_number = q.child_number
WHERE s.status = 'ACTIVE'
  AND q.elapsed_time > 600000000  -- > 10 minutes
  AND s.username IS NOT NULL
ORDER BY q.elapsed_time DESC;

Step 2: Kill a Session (Graceful)

KILL SESSION marks the session for termination. Oracle waits for the session to reach a safe point before cleaning up. The session status changes to KILLED.

-- Kill a single session
ALTER SYSTEM KILL SESSION '125,4892';
-- Format: 'SID,SERIAL#'

-- Kill a session immediately (no wait for safe point)
ALTER SYSTEM KILL SESSION '125,4892' IMMEDIATE;

The IMMEDIATE keyword returns control to you right away and Oracle kills the session in the background. Without IMMEDIATE, the command may block for seconds to minutes waiting for the session to roll back.

Step 3: Disconnect Session (OS-Level Kill)

DISCONNECT SESSION terminates the session at the OS level — faster and more forceful than KILL SESSION. Use this when KILL SESSION leaves a zombie.

-- Disconnect a session immediately (equivalent to killing the OS process)
ALTER SYSTEM DISCONNECT SESSION '125,4892' POST_TRANSACTION;
-- POST_TRANSACTION waits for the current transaction to complete first

-- Disconnect immediately without waiting
ALTER SYSTEM DISCONNECT SESSION '125,4892' IMMEDIATE;

Step 4: Kill Using the OS PID

When a session is in a KILLED state but not going away, kill the underlying OS process directly.

-- Find the OS PID for a session
SELECT s.sid, s.serial#, s.username, s.status,
       p.spid  AS os_pid,
       p.pname AS process_name
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid = 125;

# On Linux/Unix — kill the OS process
kill -9 <os_pid>

# Example: kill -9 28457

# Verify the process is gone
ps -ef | grep 28457

Step 5: Kill Sessions in RAC (Multi-Instance)

In a RAC environment, you need to specify the instance number using @INST_ID.

-- Find sessions across all RAC instances
SELECT inst_id, sid, serial#, username, status, machine
FROM gv$session
WHERE username = 'APP_USER'
  AND status = 'ACTIVE'
ORDER BY inst_id, sid;

-- Kill a session on instance 2
ALTER SYSTEM KILL SESSION '125,4892,@2' IMMEDIATE;
-- Format: 'SID,SERIAL#,@INST_ID'

-- Kill all blocking sessions across all RAC nodes
BEGIN
  FOR s IN (
    SELECT inst_id, sid, serial#
    FROM gv$session
    WHERE blocking_session IS NOT NULL
      AND last_call_et > 300  -- blocking for > 5 minutes
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# ||
      ',@' || s.inst_id || ''' IMMEDIATE';
  END LOOP;
END;
/

Advanced Examples

Kill All Sessions for a User

-- Generate KILL statements for all sessions of a specific user
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
FROM v$session
WHERE username = 'APP_USER'
  AND status NOT IN ('KILLED');

-- Execute the kills automatically
BEGIN
  FOR s IN (
    SELECT sid, serial#
    FROM v$session
    WHERE username = 'APP_USER'
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE';
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Could not kill session ' || s.sid || ': ' || SQLERRM);
    END;
  END LOOP;
END;
/

Kill Sessions Holding Specific Locks

-- Find and kill the session holding a row lock on a specific table
SELECT DISTINCT
  s.sid, s.serial#, s.username, s.status,
  s.machine, s.program, s.last_call_et AS idle_sec
FROM v$session s
JOIN v$lock l     ON s.sid = l.sid
JOIN v$locked_object lo ON s.sid = lo.session_id
JOIN dba_objects obj ON lo.object_id = obj.object_id
WHERE obj.object_name = 'ORDERS'
  AND l.type = 'TM';

-- Then kill:
ALTER SYSTEM KILL SESSION '125,4892' IMMEDIATE;

Monitor Session Kill Progress

-- Check if the killed session is cleaning up
SELECT sid, serial#, username, status, last_call_et
FROM v$session
WHERE status = 'KILLED';

-- A KILLED status that persists means the session is rolling back.
-- Do NOT re-kill it. Wait for rollback to complete.
-- The rollback time is proportional to the amount of work to undo.

Common Mistakes and Pitfalls

Killing a KILLED session again — If a session shows status = KILLED, it is already rolling back. Killing it again does nothing and may make Oracle log spurious errors. Wait for the rollback to complete.

Not using IMMEDIATE — Without IMMEDIATE, KILL SESSION waits for the remote client to acknowledge the kill, which may never happen if the client is unresponsive. Always use IMMEDIATE for hung sessions.

Killing background processes — Never kill sessions belonging to SYS with names like PMON, SMON, LGWR, DBWR, CKPT. Killing a background process crashes the database instance.

Confusing SID reuse — SID values are reused after sessions disconnect. Always use SID,SERIAL# together. The SERIAL# changes with each new occupant of a SID slot.

Killing in the wrong RAC instance — In RAC, each instance manages its own sessions. ALTER SYSTEM KILL SESSION without @INST_ID only works on sessions belonging to the instance you are connected to.

Verification Queries

-- Confirm the session is gone after kill
SELECT sid, serial#, username, status
FROM v$session
WHERE sid = 125;
-- No rows = session terminated successfully

-- Check that locks held by the killed session are released
SELECT COUNT(*) AS lock_count
FROM v$lock
WHERE sid = 125;
-- Should return 0

-- Verify blocked sessions are now unblocked
SELECT sid, serial#, username, status, blocking_session
FROM v$session
WHERE blocking_session IS NOT NULL;
-- Should show no rows blocked by the killed SID

-- Confirm the OS process is gone (Linux/Unix)
-- ps -ef | grep <spid>

Related Topics

• Oracle Errors: ORA-00028 - Your session has been killed
• Oracle Errors: ORA-00054 - Resource busy (lock conflicts)
• Oracle Errors: ORA-00060 - Deadlock detected
• Performance Analysis Scripts - Session and lock monitoring