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:
-- create a table for output
create table JOB_OUTPUT (
log_date timestamp with time zone
-- add an event queue subscriber for this user's messages
-- set the maximum run time for the jobbegin
-- create a 'sniper' job to kill MYJOB - based on eventbegin
'insert into job_output values(systimestamp, ''kill_myjob started'');
'tab.user_data.object_name = ''MYJOB'' and
tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
-- you can see in the output table if the job was killed:
select * from JOB_OUTPUT;