oracle – generate kill database sessions script

Here is the script that comes handy to Oracle DBAs for generating script to kill sessions based on user sessions.

For example, this SQL script generates the script to kill all the sessions belong to user – SCOTT.

Login to database as sys or system or sysdba or user with DBA role:


SQL> select 'alter system kill session '''||sid||','||serial#||''';' as script from v$session where username='SCOTT';

–Example output:

alter system kill session ‘2545,48723’;
alter system kill session ‘5642,317’;

Here is an example of kill session in Oracle RAC environment:


select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''';' as script from gv$session where sid =