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;
