Dynamic sql script to kill over 500 sessions

Hi
Can anyone provide me with a dynamic sql script to kill more than 500 sessions
Thanks

Quote:

---

Originally Posted by Ablakios

Hi
Can anyone provide me with a dynamic sql script to kill more than 500 sessions
Thanks

---

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.

declare
V_STRING VARCHAR2(400);
V_SID VARCHAR2(40);
V_SERIAL VARCHAR2(40);
cursor cur1 IS
select SID,SERIAL# from v$session where serial#!=1 and status!='ACTIVE' and type !='BACKGROUND';
begin
For x in cur1 LOOP
V_SID:=x.SID;
V_SERIAL:=x.SERIAL#;
V_STRING := 'ALTER SYSTEM KILL SESSION '''||V_SID||', '||V_SERIAL||'''';
EXECUTE IMMEDIATE V_STRING;
end loop;
end;
/

... 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.