RAC

“Holly slaughter, DBAtman!!”

Please write me a procedure to kill all sessions from a specific Module!
Really? Ok doke!

First lets create a control table, so we can see what is being killed and any error:

create table KILLED_SESSIONS(dt_kill date, nodesess number, 
nodejob number, username_k varchar(50), servername varchar(200), 
sid_nb number, serial_nb number,cmd varchar(300));

And let’s go to the fun part:

create or replace procedure KILL_PRC (p_module varchar2) as
  sqltext varchar2(32000) := null;
BEGIN
-- Killing Local
   FOR REC IN (select 'alter system kill session '''||sid||','||serial#||''' immediate' cmd, username, machine,sid,serial# from 
		v$session where module='''||p_module||''') LOOP
    sqltext := (rec.cmd);
    insert into SYS.KILLED_SESSIONS values (sysdate,SYS_CONTEXT ('USERENV', 'INSTANCE'),SYS_CONTEXT ('USERENV', 'INSTANCE'),rec.username,rec.machine,rec.sid,rec.serial#,rec.cmd);
    commit;
    begin
     execute immediate sqltext;
    Exception
      When Others Then
         insert into SYS.KILLED_SESSIONS(cmd) values ('ERROR_L: '||rec.cmd);
         commit;
    end;
    END LOOP;
-- Remote
   FOR REC IN (select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' ' cmd,username, machine,sid,serial#, inst_id
     from gv$session where module='''||p_module||''' and inst_id<>SYS_CONTEXT ('USERENV', 'INSTANCE') ) LOOP
    sqltext := (rec.cmd);
    insert into SYS.KILLED_SESSIONS values (sysdate,SYS_CONTEXT ('USERENV', 'INSTANCE'),rec.inst_id,rec.username,rec.machine,rec.sid,rec.serial#,rec.cmd);
    commit;
    begin
      execute immediate sqltext;
    Exception
      When Others Then
         insert into SYS.KILLED_SESSIONS(cmd) values ('ERROR_R: '||rec.cmd);
         commit;
    end;
    END LOOP;
Exception
    When Others Then
    insert into SYS.KILLED_SESSIONS(cmd) values ('ERROR_G');
    commit;
END;
/

Nice right?
Of course you can adapt this filter by user, by machine (host connected), application, service name, or what ever you want. Or even combine items. Just make sure you are not killing required SYS sessions…

With this, you can simply grant privilege on this to other users.

Oh, by the way, this can be even scheduled in a scheduler job, for periodical kills, if you want:

BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
 job_name => 'KILL_PRC_ADHOC',
 job_type => 'STORED_PROCEDURE',
 job_action => 'SYS.KILL_PRC',
 start_date => '01-JAN-19 12.00.00 AM EST5EDT',
 repeat_interval => 'FREQ=DAILY',
 end_date => null,
 auto_drop => FALSE,
 comments => 'Running Daily at midnight EST');
END;
/

exec dbms_scheduler.set_job_argument_value(job_name => 'KILL_PRC_ADHOC', argument_position => 1, argument_value => 'MYAPP');

exec dbms_scheduler.enable('SYS.KILL_PRC_ADHOC');

Also, this needs to be created in a user with the required credentials to do so.

Taking a quote from my friend Spiderman:
“With Great Power Comes Great Responsibility” (Ben, Uncle).

Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!

Tag: RAC

Procedure to Kill All Sessions from User/Module/… over a RAC and Scheduler Job to do so…