Many thanks to Ravi R. of OTN Scheduler Forum for the idea, below is the code for a general purpose sniper job that snipes any job that has exceeded its maximum duration.
--
-- this test was done on 11.1 EE
--
-- ensure that the values of are both non-NULL and sufficiently high for your need
select value
from v$parameter
where name='job_queue_processes';
VALUE
-----
1000
-- if needed dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', 10); can be used to set
select value
from all_scheduler_global_attribute
where ATTRIBUTE_NAME= 'MAX_JOB_SLAVE_PROCESSES';
VALUE
-----
10
-- create a table for output
create table job_output (a timestamp with time zone, b varchar2(1000));
-- add an event queue subscriber for this user's messages
exec dbms_scheduler.add_event_queue_subscriber('myagent')
-- create a sniper procedure
create or replace procedure sniper_proc
(message IN sys.scheduler$_event_info) as
begin
-- if this is not a JOB_OVER_MAX_DUR message, error out
if message.event_type != 'JOB_OVER_MAX_DUR' then
raise PROGRAM_ERROR;
end if;
-- stop the job
dbms_scheduler.stop_job('"'||message.object_owner||'"."'||
message.object_name ||'"');
-- insert into job output
insert into job_output values (systimestamp,
'sniper job sniped '||'"'||message.object_owner||'"."'||
message.object_name ||'"');
end;
/
-- create a sniper program
begin
dbms_scheduler.create_program (
program_name => 'sniper_prog',
program_action=> 'sniper_proc',
program_type => 'stored_procedure',
number_of_arguments => 1,
enabled => FALSE) ;
dbms_scheduler.define_metadata_argument ( 'sniper_prog','event_message',1);
dbms_scheduler.enable('sniper_prog');
end;
/
-- create a general purpose sniper job to kill any job that has
-- exceeded its max_run_duration
begin
dbms_scheduler.create_job('sniper_job',
program_name=>'sniper_prog',
event_condition =>
'tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
queue_spec =>'sys.scheduler$_event_queue,myagent',
enabled=>true);
end;
/
-- create two jobs to test the sniper job
begin
dbms_scheduler.create_job
( 'first_job', job_action =>
'insert into job_output values(systimestamp, ''first job begins'');
commit; dbms_lock.sleep(120);
insert into job_output values(systimestamp, ''first job ends'');',
job_type => 'plsql_block',
enabled => false ) ;
dbms_scheduler.set_attribute
( 'first_job' , 'max_run_duration' , interval '60' second);
dbms_scheduler.create_job
( 'second_job', job_action =>
'insert into job_output values(systimestamp, ''second job begins'');
commit; dbms_lock.sleep(120);
insert into job_output values(systimestamp, ''second job ends'');',
job_type => 'plsql_block',
enabled => false ) ;
dbms_scheduler.set_attribute
( 'second_job' , 'max_run_duration' , interval '60' second);
dbms_scheduler.enable('first_job');
dbms_lock.sleep(10);
dbms_scheduler.enable('second_job');
end;
/
SELECT * FROM job_output ORDER BY a ;
A B
------------------------------------------------- --------------------------------------------------------------------------------
03/09/2008 11:45:29,585914 +03:00 first job begins
03/09/2008 11:45:40,019922 +03:00 second job begins
03/09/2008 11:46:34,226044 +03:00 sniper job sniped "SH"."FIRST_JOB"
03/09/2008 11:46:44,344798 +03:00 sniper job sniped "SH"."SECOND_JOB"
One limitation of this is that it only works for jobs in the same schema as the sniper job.
Also there is a similar code example of a job e-mail notification package on the OTN Scheduler page, here.
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 