Dynamic sql script to kill over 500 sessions
Can anyone provide me with a dynamic sql script to kill more than 500 sessions
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
Originally Posted by Ablakios
shutdown abort will do that for you
anyway, the script depends on what you want to kill
loop through v$session, pull out the sid and serial then kill it
Modify this according to your requirement.
cursor cur1 IS
select SID,SERIAL# from v$session where serial#!=1 and status!='ACTIVE' and type !='BACKGROUND';
For x in cur1 LOOP
V_STRING := 'ALTER SYSTEM KILL SESSION '''||V_SID||', '||V_SERIAL||'''';
EXECUTE IMMEDIATE V_STRING;
... and monitor rollbacks.
Question, are you supposed to kill all sessions in the database? if Yes... you have to come up with a way to avoid new sessions get initiated most probably by stopping listeners and putting the database in restricted mode.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width