How to Kill or Disconnect a Session in Oracle: Single Instance and RAC

Managing sessions in Oracle databases is essential for database administrators. This guide provides concise commands for both single-instance and RAC (Real Application Clusters) environments to kill or disconnect sessions forcefully or gracefully.

Single-Instance Oracle

To kill a session immediately:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

To disconnect a session gracefully (waiting for transactions to complete):

ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#' POST_TRANSACTION;

To disconnect a session immediately:

ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#' IMMEDIATE;

Oracle RAC

To kill a session in RAC, include the INST_ID for the target instance:

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ',@' || inst_id || ''' IMMEDIATE;'
FROM gv$session
WHERE sid = &sid AND serial# = &serial;

For active sessions only:

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ',@' || inst_id || ''' IMMEDIATE;'
FROM gv$session
WHERE sid = &sid AND serial# = &serial AND status = 'ACTIVE';

To disconnect a session in RAC:

ALTER SYSTEM DISCONNECT SESSION ‘SID,SERIAL#,@INST_ID’ IMMEDIATE;

Key Notes

1. IMMEDIATE: Use for forceful termination or disconnection.
2. POST_TRANSACTION: Use for graceful disconnection.
3. RAC: Always include INST_ID to specify the instance in multi-instance environments.

These commands enable efficient management of sessions across both single-instance and RAC Oracle environments.