MAX_RUN_DURATION attribute of the SCHEDULER to setup a sniper job

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.