Oracle Kill Long Running Sessions Job

Hi,

Sometimes you need killing some long running sessions that coming from specific machines and specific Queries.

If you want to kill some long running sessions that coming from specific machines and specific Queries, then you can create dbms Oracle job for this.

Before creating Oracle dbms job, firstly we need to create procedure which will perform this job, then create dbms_scheduler job and use this procedure to kill related sessions.

I will keep session informations in the kill_sessions_info table, so I have create it like following.

create table sys.kill_sessions_info(killId number,username varchar2(100),stext varchar2(100),stime date,duration date)

Then create Procedure like following. This procedure will kill sessions that coming from ‘MEHMETSALIH\DEVECI’ machine and running more than 1200 second and sql_text not like ‘%SALES%’. You can change these conditions according to your needs.

CREATE OR REPLACE procedure SYS.kill_long_sessions as
BEGIN

FOR i IN (select s.sid||','||s.serial# killId,s.username username,ss.sql_fulltext stext,s.logon_time stime,s.last_call_et durat from 
v$session s, v$sql ss where s.machine='MEHMETSALIH\DEVECI' and s.last_call_et>1200 and
s.sql_id=ss.sql_id and ss.sql_fulltext not like '%SALES%' )

LOOP
declare
l_error VARCHAR2(2000):='';
begin
insert into sys.kill_sessions_info values(i.killId,i.username,i.stext,i.stime,i.durat);
commit;
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION '''||i.killId||''' IMMEDIATE';
EXCEPTION
WHEN OTHERS THEN
l_error:=SQLERRM;

end;
END LOOP;

COMMIT;

END;
/

After creating procedure, you can create DBMS_SCHEDULER job like following.

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SYS.KILL_LONG_SESSION'
,start_date => TO_TIMESTAMP_TZ('2018/12/06 16:00:00.000000 +03:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
,repeat_interval => 'FREQ=MINUTELY;INTERVAL=1'
,end_date => TO_TIMESTAMP_TZ('2090/08/18 00:00:00.000000 +03:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => 'SYS.KILL_LONG_SESSIONS'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'MAX_RUNS');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'AUTO_DROP'
,value => FALSE);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'SYS.KILL_LONG_SESSION');
END;
/

you can change interval and start time according to your needs.