Unfortunately, I couldn't get the example to work for event
JOB_OVER_MAX_DUR in Oracle 10gR2 on one particular box.
What I've tried is:
-- create log table 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 -- insert into job_output values (systimestamp,'sniper job started for ' ||message.event_type||' from ' ||'"'||message.object_owner||'"."'||message.object_name ||'"'); commit; 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 log any job that has -- raised an event begin dbms_scheduler.create_job('sniper_job', program_name=>'sniper_prog', event_condition => 'tab.user_data.event_type = ''JOB_OVER_MAX_DUR'' OR tab.user_data.event_type = ''JOB_SUCCEEDED''', queue_spec =>'sys.scheduler$_event_queue,myagent', enabled=>true); end; / -- create job 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 ) ; -- set max runtime dbms_scheduler.set_attribute ( 'first_job' , 'max_run_duration' , interval '60' second); -- set all events to be raised (for debugging) dbms_scheduler.set_attribute( name => 'first_job', attribute => 'raise_events', value => dbms_scheduler.job_all_events); -- start the job dbms_scheduler.enable('first_job'); end; /
For some reason the event
JOB_OVER_MAX_DUR is either not raised or the sniper_job doesn't receive it. Only
JOB_SUCCEEDED is received.
My log table looks like:
SELECT * FROM job_output ORDER BY 1 DESC; 25.07.2012 10:39:11,475879 +02:00 sniper job started for JOB_SUCCEEDED from "SCOTT"."FIRST_JOB" 25.07.2012 10:39:10,155557 +02:00 first job ends 25.07.2012 10:37:10,142660 +02:00 first job begins
So no event
JOB_OVER_MAX_DUR was received.
Any ideas what I am doing wrong or which init parameters I have to check/set or how to debug these events?
Edit: I'm interested why this isn't working in 10g. This example is working fine in 11g but not in 10g and I don't see any reason why.
Edit 2: The example is working fine at a different 10g box, but on the intended one it's not. I've checked all the
%scheduler% dictionary views, but none of them gives details on which events have been raised. Any ideas/hints how to debug scheduler events? In this case I have DBA rights and OS server level access to debug.