Skip to content

Generate Kill Session Statements (kill_session.gen)

What This Script Does

This generator script creates ALTER SYSTEM KILL SESSION statements to terminate database sessions. It handles both single-instance and RAC environments, filtering by username and program.

The Script

rem  kill_session.gen
rem
select 'alter system kill session ''' || sid || ',' || serial# || '''' ||
       decode(inst_id, userenv('instance'), '', ' @' || inst_id) || ' immediate;'
  from gv$session
 where username like upper('&username')
   and program like upper('&program')
   and username is not null
   and type = 'USER'
 order by inst_id, username, sid
/

Usage

-- Basic usage
@kill_session.gen

-- When prompted, enter:
-- username: Username pattern (use % for wildcard)
-- program: Program name pattern (use % for wildcard)

Parameters

The script prompts for:

&username - Username pattern (use % for wildcard)
&program - Program name pattern (use % for wildcard)

Required Privileges

SELECT ON GV$SESSION
ALTER SYSTEM privilege

Sample Output

alter system kill session '125,45678' immediate;
alter system kill session '256,23456' immediate;
alter system kill session '384,34567' @2 immediate;

Key Features

RAC Aware: Handles multi-instance environments with @instance syntax
IMMEDIATE Option: Forces immediate session termination
User Sessions Only: Excludes background processes
Flexible Filtering: Filter by username and/or program

Common Use Cases

Kill All Sessions for Specific User

@kill_session.gen
-- Enter: APP_USER for username, % for program

Kill Sessions from Specific Application

@kill_session.gen
-- Enter: % for username, sqlplus% for program

Active Sessions - View active sessions before killing
Blocking Locks - Find blocking sessions