Kill and Disconnect Oracle Sessions with ALTER SYSTEM

calendar Jul 20, 2026 / · 10 min read · Oracle DBA ALTER SYSTEM Kill Session Disconnect Session V$SESSION Oracle Administration Session Termination ORA-00031  ·
Share on: linkedin copy

Kill and Disconnect Oracle Sessions with ALTER SYSTEM

Purpose

V$SESSION exposes the SID and SERIAL# pair that both ALTER SYSTEM KILL SESSION and ALTER SYSTEM DISCONNECT SESSION require as their targeting key — a DBA cannot terminate a stuck session without pulling that pair first, and getting it wrong means terminating the wrong connection on an instance where SIDs are reused constantly. Once a session is confirmed as the target — a runaway report query burning CPU, an abandoned connection holding a row lock, an idle session pinning undo — the question shifts from which session to how to end it.

ALTER SYSTEM gives two different termination commands, and they behave differently enough that picking the wrong one produces a session that looks dead but isn't, or a rollback that takes longer than the problem it was meant to fix. KILL SESSION marks the session for termination and, when qualified with IMMEDIATE, forces the rollback of any uncommitted work right away, notifying the background process PMON to clean up the session's resources as soon as it can get to them. DISCONNECT SESSION targets the underlying server process rather than the session state and is the correct tool when the session's Oracle process is gone but the OS-level connection is still attached — a common symptom after a client machine crashes or a network path drops without a clean TCP close. DISCONNECT SESSION accepts a POST_TRANSACTION qualifier as an alternative to IMMEDIATE: it lets any current transaction finish and commit or roll back on its own timeline before the disconnect happens, rather than forcing an immediate rollback.

This post covers the syntax for both commands, when IMMEDIATE is the right call versus when POST_TRANSACTION avoids unnecessary rollback cost, and the ORA-00031: session marked for kill error that shows up when a kill command is reissued against a session still mid-cleanup. It is the termination counterpart to a blocking-sessions diagnostic: a blocking-session query finds which SID is holding a lock and blocking others; this post ends that session once it has been identified.

Code

 1-- Step 1: identify the session, sourcing SID, SERIAL#, and status from V$SESSION
 2SELECT s.sid,
 3       s.serial#,
 4       s.username,
 5       s.status,
 6       s.blocking_session,
 7       s.program,
 8       s.logon_time,
 9       s.last_call_et
10FROM   v$session s
11WHERE  s.username = 'APPUSER'      -- filter by username, SID, or blocking_session
12  AND  s.status IN ('ACTIVE', 'INACTIVE')
13ORDER  BY s.last_call_et DESC;
14
15-- Step 2a: kill the session, rolling back any transaction immediately
16ALTER SYSTEM KILL SESSION '142,8821' IMMEDIATE;
17
18-- Step 2b: disconnect the server process, letting the current transaction finish first
19ALTER SYSTEM DISCONNECT SESSION '142,8821' POST_TRANSACTION;
20
21-- Step 2c: disconnect the server process right away, no wait for the transaction
22ALTER SYSTEM DISCONNECT SESSION '142,8821' IMMEDIATE;
23
24-- Step 3: confirm the session status after issuing the kill
25SELECT s.sid,
26       s.serial#,
27       s.status,
28       s.username
29FROM   v$session s
30WHERE  s.sid = 142;
31-- STATUS = 'KILLED' means the session is marked; PMON has not finished cleanup yet.
32-- No row returned means PMON has completed the cleanup and the session is gone.
33
34-- RAC: target a session on a specific instance by appending the instance ID
35ALTER SYSTEM KILL SESSION '142,8821,@2' IMMEDIATE;

Code Breakdown

Step 1: sourcing SID and SERIAL# from V$SESSION

V$SESSION.SID and V$SESSION.SERIAL# together form the exact key both ALTER SYSTEM commands need. SID alone is not a safe identifier — Oracle reuses SID values as sessions log off and new ones log on, so a SID that pointed at a stuck report query five minutes ago can point at an unrelated new connection now. SERIAL# increments with every reuse of a SID, so the pair together always identifies one specific, current session. STATUS, BLOCKING_SESSION, PROGRAM, and LAST_CALL_ET (elapsed seconds since the session's last call) give the context needed to confirm this is the right session before issuing a termination command against it.

Step 2a: KILL SESSION IMMEDIATE

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE is the standard command for ending a session whose Oracle-side process is still alive and responsive. The IMMEDIATE qualifier tells Oracle to roll back any uncommitted transaction right away rather than waiting. Oracle marks the session for termination and the background process PMON performs the actual cleanup — releasing locks, freeing the process, and removing the session's entry from V$SESSION — as soon as it is scheduled to run. On a lightly loaded instance this happens in a fraction of a second; on a busy instance with PMON occupied elsewhere, or a session mid-rollback of a very large transaction, cleanup can take noticeably longer.

Step 2b and 2c: DISCONNECT SESSION with POST_TRANSACTION or IMMEDIATE

ALTER SYSTEM DISCONNECT SESSION targets the dedicated server process associated with the session rather than the session's logical state. It is the right tool when the client connection is gone at the network or OS level but the server-side process Oracle allocated for that client is still holding resources — the classic symptom after a client machine loses power or a VPN drops without a graceful disconnect. POST_TRANSACTION lets any transaction the session was mid-way through commit or roll back through its normal completion path before the server process is released, avoiding an unnecessary forced rollback of work that was about to finish anyway. IMMEDIATE skips that wait and forces the disconnect — and any needed rollback — right away, which is the correct choice when the session is confirmed dead at the network layer and there is no reasonable expectation the transaction will complete on its own.

Step 3: confirming termination

Re-querying V$SESSION for the same SID after issuing a kill shows one of two states. A STATUS of KILLED means Oracle has marked the session for termination but PMON has not yet finished the actual cleanup — the row is still present but the session is not accepting new work. No row returned at all means cleanup is complete and the session no longer exists in V$SESSION.

RAC: targeting a specific instance

On a Real Application Clusters configuration, a session belongs to one specific instance. Appending @<instance_id> to the SID,SERIAL# string in the ALTER SYSTEM command — for example '142,8821,@2' — targets the session on instance 2 directly from any instance in the cluster, without needing to connect to that specific instance first.

Key Points

  • KILL SESSION and DISCONNECT SESSION target different things. KILL SESSION acts on the session's logical state inside Oracle; DISCONNECT SESSION acts on the underlying server process. A session whose client vanished at the network level, leaving an orphaned server process, is a DISCONNECT SESSION case even though KILL SESSION will often also clear it.
  • SID alone is never a safe kill target. Always pair it with SERIAL# from the same V$SESSION query used to identify the session, run immediately before the kill, to avoid a SID reuse race.
  • STATUS = 'KILLED' is a marked-for-kill state, not a completed termination. PMON performs the actual resource release asynchronously; a KILLED row can persist briefly before it disappears from V$SESSION.
  • ORA-00031: session marked for kill fires on a repeat kill attempt. Reissuing KILL SESSION against a SID,SERIAL# that is already in the KILLED state and mid-cleanup returns this error rather than silently succeeding again.
  • RAC sessions require the @instance_id suffix when killed from a different instance. Omitting it when the session lives on another node in the cluster returns an error rather than terminating the session.
  • A very large uncommitted transaction extends cleanup time regardless of IMMEDIATE. IMMEDIATE starts the rollback immediately, but the rollback itself still has to undo every change the transaction made — a multi-hour batch load killed near its end can take a comparable amount of time to roll back.

Insights and Best Practices

When IMMEDIATE beats POST_TRANSACTION

IMMEDIATE is the right default for KILL SESSION in almost every case — there is rarely a reason to let a session's uncommitted work continue once the decision to terminate it has been made. For DISCONNECT SESSION, the choice is less automatic. POST_TRANSACTION is worth using when the session is known to be mid-transaction and the transaction is expected to finish quickly on its own — letting it complete avoids a rollback that costs more time than simply waiting. IMMEDIATE is the right call for DISCONNECT SESSION when the client is confirmed gone (a dropped VPN, a crashed application server) and there is no expectation the transaction will ever resume progress toward a natural commit.

Reading the ORA-00031 wait

ORA-00031 is not a failure of the kill command itself — it means the previous kill request is still being processed. The practical response is to wait and re-check V$SESSION rather than immediately reissuing the kill. Querying STATUS for the same SID,SERIAL# on a short interval — a few seconds apart — shows whether the row eventually disappears (cleanup completed) or persists indefinitely (cleanup is stuck, which usually points at a large rollback still in progress or PMON itself under load). Repeatedly reissuing KILL SESSION against a session already in the KILLED state does not speed up cleanup; it only produces more ORA-00031 errors.

RAC considerations

On RAC, confirm which instance owns the target session before issuing the kill. V$SESSION.INST_ID in the cluster-wide view GV$SESSION shows the owning instance directly, which avoids guessing. A kill issued without the @instance_id suffix against a session that belongs to a different instance than the one you are connected to will not terminate the intended session.

Verifying termination completed

Treat the disappearance of the row from V$SESSION — not the successful execution of the ALTER SYSTEM statement — as the actual confirmation that termination completed. The ALTER SYSTEM command itself only confirms Oracle accepted the request; it does not wait for PMON cleanup to finish before returning control to the DBA session that issued it. For a session holding a lock that another process is waiting on, re-check the blocking session's own downstream effect — has the blocked session's wait cleared — as the final confirmation that the termination fully resolved the underlying problem, not just that the target row vanished from V$SESSION.

When to Run This

  • A runaway ad-hoc query is consuming CPU or I/O far beyond its expected footprint and needs to stop immediately.
  • A session is confirmed as the blocker in a lock-contention diagnostic and holding up other work.
  • A client machine or network path is known to have dropped, leaving an orphaned server-side process still attached to the instance.
  • An idle session has held undo or a long-running transaction open well past any reasonable business need, pinning resources other sessions require.
  • A maintenance window requires clearing all non-essential sessions before a planned shutdown or upgrade step.
  • A session appears twice in monitoring because a client reconnected without the previous connection ever closing cleanly.

Troubleshooting Common Issues

ORA-00031: session marked for kill on a repeat attempt. The session is already in the KILLED state from a prior kill request and PMON has not finished cleanup. Re-query V$SESSION for the same SID,SERIAL# after a short wait instead of reissuing the kill; the row will clear once cleanup completes.

The session row disappears from V$SESSION but the resource it was holding — a lock, a piece of undo — does not appear to be released. Confirm the check is being run against a fresh query, not a cached result set, and check whether a downstream blocked session's wait has actually cleared rather than only checking the killed session's own row.

ALTER SYSTEM DISCONNECT SESSION returns successfully but the session reappears shortly after. This usually means the client reconnected using a connection pool or auto-reconnect logic rather than the original connection surviving. Confirm whether the reappearing session is a genuinely new connection (a fresh SID,SERIAL# pair and LOGON_TIME) before assuming the disconnect failed.

A kill against a RAC session appears to have no effect. Confirm the target session's owning instance with GV$SESSION.INST_ID and reissue the command with the correct @instance_id suffix if the session lives on a different node than the one the kill was issued from.

References

Posts in this series