KILL MULTIPLE SESSIONS IN ONE GO ‘SQL SCRIPT’ ORACLE

You are in situation to quickly kill multiple sessions which consume high resource on database which cause performance issues. You can use the below scripts to quickly act based on your scenario

SQL script to kill all the sessions from the database — Note: Use this script with caution as this will kill all the sessions from the database

SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE;' FROM v$session;

SQL script to kill all the sessions run with username called TEST

SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%';

SQL script to kill all the sessions which run with user TEST and which are INACTIVE

SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%' AND STATUS='INACTIVE'

SQL script to kill all the sessions which run with user TEST and which are INACTIVE and run for more than 10000 seconds. You can modify the script according to your scenario

SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%' AND STATUS='INACTIVE' and LAST_CALL_ET > 10000;

SQL script to kill all the sessions which run with user TEST and which are INACTIVE and run for more than 10000 seconds and logged on users from last 24 hours

SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%' AND STATUS='INACTIVE' and LAST_CALL_ET > 10000 and LOGON_TIME > sysdate - 1 ;

SQL script to kill all the sessions which run sql queries

SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE;' FROM v$session WHERE SQL_ID is NOT NULL;

SQL script to kill all the sessions which has blockings

SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE;' FROM v$session WHERE BLOCKING_SESSION is NOT NULL;



SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE;' FROM v$session WHERE CLIENT_INFO LIKE '%rman%;

SAMPLE OUTPUT:



kish@exdbx<>spool killscript.sql
kish@exdbx<>SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE;' FROM v$session;

'ALTERSYSTEMKILLSESSION'||SID||','||SERIAL#||'IMMEDIATE;'
----------------------------------------------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION 1,15 IMMEDIATE;
ALTER SYSTEM KILL SESSION 2,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 3,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 4,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 5,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 6,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 7,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 8,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 9,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 10,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 11,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 12,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 13,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 14,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 16,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 17,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 18,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 19,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 20,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 21,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 22,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 23,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 24,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 25,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 26,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 27,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 28,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 30,643 IMMEDIATE;
ALTER SYSTEM KILL SESSION 31,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 32,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 34,15 IMMEDIATE;
ALTER SYSTEM KILL SESSION 36,9 IMMEDIATE;
ALTER SYSTEM KILL SESSION 37,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 39,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 40,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 41,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 46,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 47,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 48,5 IMMEDIATE;
ALTER SYSTEM KILL SESSION 51,1 IMMEDIATE;
ALTER SYSTEM KILL SESSION 56,5 IMMEDIATE;
ALTER SYSTEM KILL SESSION 58,125 IMMEDIATE;
ALTER SYSTEM KILL SESSION 59,9 IMMEDIATE;
ALTER SYSTEM KILL SESSION 62,491 IMMEDIATE;

44 rows selected.



kish@exdbx<>@killscript.sql

Hope this help you in any urgent situation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s