Procédure Kill session sous SYS #oracle #sql

Procédure Kill session sous SYS.md

create or replace procedure kill_all_sessions
( schema_in in varchar2 , status out number )
as
lv_status varchar2(30);
pn_sid number;
pn_serial number;
cursor c1 is select sid,serial# from v$session where username = upper(schema_in);
godlike exception;
PRAGMA EXCEPTION_INIT(godlike, -20000);
begin
status := 0;
select profile into lv_status from sys.dba_users where username = upper(schema_in);
if ( lv_status = 'APPLICATION' )
then
execute immediate ('alter user ' || upper(schema_in) || ' account lock');
open c1;
loop
fetch c1 into pn_sid, pn_serial;
exit when c1%NOTFOUND;
begin
execute immediate 'alter system kill session '''||pn_sid||','||pn_serial||''' immediate';
exception
when OTHERS then
null;
end;
end loop;
close c1;
select distinct 'X' into lv_status from v$session where username = upper(schema_in);
while ( lv_status = 'X' )
loop
select distinct 'X' into lv_status from v$session where username = upper(schema_in);
begin
sys.dbms_lock.sleep(5);
end;
end loop;
else
raise_application_error(-20000,'Godlike kill not permited !');
end if;
exception
when NO_DATA_FOUND then
status := -1;
when godlike then
status := -2;
when OTHERS then
status := -3;
null;
end;
/
show error


Utilisation :


var a number;
execute sys.kill_all_sessions('KILLSESSION',:a);
print a: