Starting from this question and this example I've tried to create an event based dbms_scheduler
job, which waits for event JOB_OVER_MAX_DUR
raised from jobs, which exceeded their max_run_duration
.
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.