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