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
Component | Description |
---|---|
ALTER SYSTEM | A 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_TRANSACTION | Optional for DISCONNECT; disconnects after the current transaction completes (graceful). |
IMMEDIATE | Forces rollback and resource release instantly. |
NOREPLAY | Prevents session recovery under Application Continuity (AC). |
Placeholders Explained
Placeholder | Meaning |
---|---|
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
Command | Behavior |
---|---|
KILL SESSION | Marks session for termination, waits for ongoing transactions unless IMMEDIATE is specified. |
DISCONNECT SESSION | Terminates 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:
SID | SERIAL# |
---|---|
301 | 45211 |
402 | 87452 |
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
orIMMEDIATE
withDISCONNECT SESSION
, otherwise the command fails.
Quick Reference Table
Scenario | Command 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 recovery | ALTER SYSTEM KILL SESSION 'SID, SERIAL#' NOREPLAY; |
Normal disconnect | ALTER SYSTEM DISCONNECT SESSION 'SID, SERIAL#' POST_TRANSACTION; |
Immediate disconnect | ALTER 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.