Tuesday 7 August 2018

Query to Kill Multiple Sessions Together | Oracle Database

How to kill multiple sessions simultaneously in Oracle Database?


Query to generate SQL statements when inactive/active sessions are very high and we need to kill lot of them



When any schema inside Oracle Database is having multiple sessions and most of them are inactive then you can use the below mentioned query to generate session kill statements for you for that particular schema with the SID and SERIAL details. Check this,


SQL> select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' from V$session where username = 'SCOTT' and last_call_et> 60 and status = 'ACTIVE';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;' 
------------------------------------------------------------------------------------------------------------------------
alter system kill session '20,19313' immediate;                                                                                     
alter system kill session '25,57183' immediate;
alter system kill session '58,36475' immediate; 

No comments:

Post a Comment