Session Killer Procedure for Developers in Oracle Database

Hi,

I will share with you Session Killer Procedure for Developers in Oracle Database in this post.

Oracle DBA gets lots of Session Kill requests from Developers and etc daily. So Oracle DBA can create any procedure for this task and give this procedure to the Developers and they can kill their sessions without DBA.

You can use and create the following Procedure to create this Session Killer procedure as follows.

CREATE OR REPLACE PROCEDURE msdba.session_killer
(pn_sid number default null ,pn_serial number default null , pn_inst_id number default null,p_object_name varchar2 default null)
as
lv_user varchar2(30);
v_stmt varchar2(500);
current_session exception;
PRAGMA EXCEPTION_INIT(current_session, -27);
begin

if p_object_name is not null and pn_sid is null or pn_serial is null then
for rec in (SELECT /*+ parallel (16) rule*/ distinct 'kill -9 '||p.spid kill_spid, 'alter system disconnect session '''||s.sid||','||s.serial#||',@'||s.inst_id||''' immediate' killsess , a.inst_id, a.session_id, 
a.oracle_username oracle_username, 
a.os_user_name os_user_name, 
b.owner owner, 
b.object_name object_name, 
b.object_type, 
DECODE(a.locked_mode, 0, '0 - NONE: lock requested but not yet obtained', 
1, '1 - NULL', 
2, '2 - ROWS_S (SS): Row Share Lock', 
3, '3 - ROW_X (SX): Row Exclusive Table Lock', 
4, '4 - SHARE (S): Share Table Lock', 
5, '5 - S/ROW-X (SSX): Share Row Exclusive Table Lock', 
6, '6 - Exclusive (X): Exclusive Table Lock') LOCKED_MODE 
FROM gv$locked_object a, 
dba_objects b , 
gv$session s, gv$process p 
WHERE a.object_id = b.object_id and b.object_name=p_object_name and 
p.addr (+) = s.paddr and s.TYPE!='BACKGROUND' and s.sid=a.session_id and s.inst_id=a.inst_id 
order by 3,4,5)

loop
begin
execute immediate rec.killsess;
dbms_output.put_line(rec.killsess||' : It was killed');
dbms_output.put_line(rec.oracle_username||' : '||rec.os_user_name||' : ' ||rec.owner||' : ' ||rec.LOCKED_MODE||' : ' ||rec.object_name||': It was killed');
exception when current_session then
dbms_output.put_line('current_session ');
when others then 
-- raise_application_error(-20001,'session is not killed '||rec.a||sqlerrm);
null;
dbms_output.put_line('sqlerrm: '||sqlerrm);
continue ;
end ;
end loop;
return ;
end if;


if p_object_name is null and pn_sid is null or pn_serial is null then
for rec in (SELECT 'alter system disconnect session '''||sid||','||serial#||',@'||inst_id||''' immediate' a
FROM gv$session
WHERE osuser = SYS_CONTEXT ('userenv', 'os_user') --and username=SYS_CONTEXT ('userenv', 'SESSION_USER')
and username not in ('SYS','MSDBA','SYSTEM'))

loop
begin
execute immediate rec.a;
dbms_output.put_line(rec.a||' : It was killed');
exception when current_session then
dbms_output.put_line('current_session ');
when others then 
-- raise_application_error(-20001,'session is not killed '||rec.a||sqlerrm);
null;
continue ;
end ;
end loop;
return ;
end if; 
select username into lv_user from gv$session where osuser=SYS_CONTEXT ('userenv', 'os_user') --and username=SYS_CONTEXT ('userenv', 'SESSION_USER') 
and sid = pn_sid and serial# = pn_serial and inst_id=pn_inst_id;
if p_object_name is null and lv_user is not null and lv_user not in ('SYS','MSDBA','SYSTEM') then
dbms_output.put_line('lv_user: '||lv_user);
v_stmt:='alter system disconnect session '''||pn_sid||','||pn_serial||',@'||pn_inst_id||''' immediate';
dbms_output.put_line('It was killed : '||v_stmt);
execute immediate v_stmt;
else
raise_application_error(-20000,'Attempt to kill protected MSDBA session has been blocked.Contact [email protected] ');
end if; 
exception when current_session then
raise_application_error(-20002,'You cannot disconnect this session :'||v_stmt);
when no_data_found then 
dbms_output.put_line('Session is not found: '||'sid:'||pn_sid||'-'||'serial:'||pn_serial||'pn_inst_id:'||pn_inst_id );
when others then 
dbms_output.put_line('sqlerrm: '||sqlerrm);
end;
/

When you create this procedure without error, any developer can use it as follows.

exec msdba.session_killer (SID,SERIAL);


exec msdba.session_killer (1214,16370);

You can read the following post to learn more details about Find Blocking Sessions and Lock Kill Scripts & Locked Objects.

https://ittutorial.org/oracle-dba-scripts-all-in-one-3-blocking-sessions-and-lock-kill-scripts/

g