Oracle Kill Session – ALTER SYSTEM KILL Session

Database administrators often face sessions that hang, lock resources, or consume unnecessary memory. Oracle provides multiple ways to terminate or disconnect sessions, depending on the scenario. In this guide, we’ll cover ALTER SYSTEM KILL SESSION and ALTER SYSTEM DISCONNECT SESSION, explain the syntax in detail, and provide examples for both single-instance and RAC (Real Application Clusters) environments.

Understanding the Syntax

Oracle uses the following syntax to end sessions:

ALTER SYSTEM { 
    DISCONNECT SESSION 'integer1, integer2' [ POST_TRANSACTION ] 
    | KILL SESSION 'integer1, integer2 [,@integer3]' 
} [ IMMEDIATE | NOREPLAY ];

Explanation of Each Component

ComponentDescription
ALTER SYSTEMA system-level command used to modify database behavior, here for terminating or disconnecting sessions.
KILL SESSION 'SID, SERIAL# [, @INST_ID]'Marks a session for termination, rolls back uncommitted transactions, and releases locks.
DISCONNECT SESSION 'SID, SERIAL#'Disconnects a session by killing the server process or virtual circuit.
POST_TRANSACTIONOptional for DISCONNECT; disconnects after the current transaction completes (graceful).
IMMEDIATEForces rollback and resource release instantly.
NOREPLAYPrevents session recovery under Application Continuity (AC).

Placeholders Explained

PlaceholderMeaning
SID (integer1)Session ID, unique per session in the instance.
SERIAL# (integer2)Serial number of the session, used with SID to uniquely identify a session.
@INST_ID (integer3)Optional instance ID for RAC; specifies which instance the session belongs to.

Kill Session vs Disconnect Session

CommandBehavior
KILL SESSIONMarks session for termination, waits for ongoing transactions unless IMMEDIATE is specified.
DISCONNECT SESSIONTerminates the server process or virtual circuit; can be immediate or post-transaction.
  • KILL SESSION → Less aggressive, preferred for normal terminations.
  • DISCONNECT SESSION → More forceful, use carefully.

Practical Examples with Randomized Placeholders

1. Normally Kill a Session in the Current Instance

Retrieve session identifiers from V$SESSION:

SELECT sid, serial# 
FROM v$session 
WHERE username = 'HR_APP01' 
  AND lower(machine) = lower('LAPTOP-XY12AB') 
  AND lower(program) LIKE lower('%sqlplus%');

Example output:

SIDSERIAL#
30145211
40287452

Kill sessions:

ALTER SYSTEM KILL SESSION '301, 45211';
ALTER SYSTEM KILL SESSION '402, 87452';

2. Kill a Session in RAC (Specify Instance)

Use GV$SESSION for RAC:

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ', ' || serial# || ', @' || inst_id || ''';' AS stmt
FROM gv$session
WHERE username = 'HR_APP01'
  AND lower(machine) = lower('LAPTOP-XY12AB')
  AND lower(program) LIKE lower('%sqlplus%');

Example output:

ALTER SYSTEM KILL SESSION '1203, 3345, @2';
ALTER SYSTEM KILL SESSION '1804, 7721, @3';

3. Immediately Kill a Session

  • Current instance:
ALTER SYSTEM KILL SESSION '301, 45211' IMMEDIATE;
  • RAC instance:
ALTER SYSTEM KILL SESSION '1203, 3345, @2' IMMEDIATE;

4. Kill Session Without Application Continuity Recovery

  • Current instance:
ALTER SYSTEM KILL SESSION '301, 45211' NOREPLAY;
  • RAC instance:
ALTER SYSTEM KILL SESSION '1203, 3345, @2' NOREPLAY;

5. Disconnect a Session

  • Normally (after transaction completes):
ALTER SYSTEM DISCONNECT SESSION '301, 45211' POST_TRANSACTION;
  • Immediately (forceful termination):
ALTER SYSTEM DISCONNECT SESSION '301, 45211' IMMEDIATE;

Important: Always specify POST_TRANSACTION or IMMEDIATE with DISCONNECT SESSION, otherwise the command fails.

Quick Reference Table

ScenarioCommand Example
Normal kill (current instance)ALTER SYSTEM KILL SESSION 'SID, SERIAL#';
Normal kill (RAC instance)ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @INST_ID';
Immediate kill (current instance)ALTER SYSTEM KILL SESSION 'SID, SERIAL#' IMMEDIATE;
Immediate kill (RAC instance)ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @INST_ID' IMMEDIATE;
Kill without AC recoveryALTER SYSTEM KILL SESSION 'SID, SERIAL#' NOREPLAY;
Normal disconnectALTER SYSTEM DISCONNECT SESSION 'SID, SERIAL#' POST_TRANSACTION;
Immediate disconnectALTER SYSTEM DISCONNECT SESSION 'SID, SERIAL#' IMMEDIATE;

Conclusion

Oracle provides flexible ways to manage sessions in both single-instance and RAC environments. By understanding the syntax, placeholders (SID, SERIAL#, INST_ID), and the different scenarios, DBAs can safely terminate or disconnect sessions without impacting database stability. This guide ensures you have all scenarios covered, from normal termination to immediate disconnection, with RAC support and Application Continuity considerations.