How to generate KILL SESSION statements in Oracle

Five recipes for generating KILL SESSION statements you can review before running. Each one prints the ALTER SYSTEM KILL SESSION statements — nothing is killed until you copy and execute them.

Preview the generated statements before running. IMMEDIATE terminates sessions and rolls back their open transactions. A broad filter (e.g. all INACTIVE) may include useful sessions — inspect the list first.

-- 1) By SQL_ID (RAC-aware — includes the instance number)
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ',@' || inst_id || ''' IMMEDIATE;'
FROM   gv$session
WHERE  sql_id = '&&sql_id';

-- 2) By username
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
FROM   v$session
WHERE  username = UPPER('&&username');

-- 3) All INACTIVE sessions
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
FROM   v$session
WHERE  status = 'INACTIVE';

-- 4) All SNIPED sessions
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
FROM   v$session
WHERE  status = 'SNIPED';

-- 5) Blocking chain: kill the blockers and everything they block
SET PAGESIZE 15000
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
FROM   v$session
WHERE  blocking_session_status = 'VALID'
   OR  sid IN (SELECT blocking_session
               FROM   v$session
               WHERE  blocking_session_status = 'VALID');