KILL MULTIPLE SESSIONS IN ONE GO ‘SQL SCRIPT’ ORACLE

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;

Script to kill RMAN jobs

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 '39,6703' IMMEDIATE;
ALTER SYSTEM KILL SESSION '40,64985' IMMEDIATE;
ALTER SYSTEM KILL SESSION '41,843' IMMEDIATE;
ALTER SYSTEM KILL SESSION '42,59804' IMMEDIATE;
ALTER SYSTEM KILL SESSION '43,24374' IMMEDIATE;
ALTER SYSTEM KILL SESSION '46,60751' IMMEDIATE;
ALTER SYSTEM KILL SESSION '48,51511' IMMEDIATE;
ALTER SYSTEM KILL SESSION '51,47493' IMMEDIATE;
ALTER SYSTEM KILL SESSION '53,29715' IMMEDIATE;
ALTER SYSTEM KILL SESSION '86,4384' IMMEDIATE;
ALTER SYSTEM KILL SESSION '87,7961' IMMEDIATE;

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'
--------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION '91,51159' IMMEDIATE;
ALTER SYSTEM KILL SESSION '94,4385' IMMEDIATE;
ALTER SYSTEM KILL SESSION '98,41974' IMMEDIATE;
ALTER SYSTEM KILL SESSION '100,31529' IMMEDIATE;
ALTER SYSTEM KILL SESSION '104,34277' IMMEDIATE;
ALTER SYSTEM KILL SESSION '117,46237' IMMEDIATE;
ALTER SYSTEM KILL SESSION '119,27082' IMMEDIATE;
ALTER SYSTEM KILL SESSION '120,51993' IMMEDIATE;

spool off


kish@exdbx<>@killscript.sql

Hope this help you in any urgent situation!