29 March 2016

kill repeating job when running longer than n minutes


Some repeating jobs may be running in a loop now and then, or should be killed after N minutes for whatever reason you may think off.

You can raise an event when a scheduled job is running longer than N minutes, but this event has to be handled. Otherwise, the job will just continue to run.

Below a small example how to set this up for MYJOB. Important to run from the job owner!

--connect as jobowner: 
conn jobowner/password

-- create a table for output
create table JOB_OUTPUT (
    log_date timestamp with time zone
   ,output varchar2(4000));

-- add an event queue subscriber for this user's messages

exec dbms_scheduler.add_event_queue_subscriber('myagent');

-- set the maximum run time for the jobbegin
  dbms_scheduler.set_attribute(name=>'MYJOB',attribute=>'max_run_duration',value=> numtodsinterval(120,'minute'));
end;
/

-- create a 'sniper' job to kill MYJOB - based on eventbegin
dbms_scheduler.create_job
 ('KILL_MYJOB',
  job_type=>'plsql_block',
  job_action=>

      'insert into job_output values(systimestamp, ''kill_myjob started'');
       dbms_scheduler.stop_job(''KILL_MYJOB'',false);',
  event_condition =>
      'tab.user_data.object_name = ''MYJOB'' and
       tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
  queue_spec =>'sys.scheduler$_event_queue,myagent',
  enabled=>true);
end;
/

-- you can see in the output table if the job was killed: 

select * from JOB_OUTPUT;