This discussion is archived
0 Replies Latest reply: Jun 27, 2013 12:54 AM by user11111706 RSS

DBMS_SCHEDULER: JOB_OVER_MAX_DUR raised, even if max duration does not exceed yet

user11111706 Newbie
Currently Being Moderated

I created some jobs with DBMS_SCHEDULER and set attribute max_run_duration to be able to stop and restart the job with a sniper job, in case of exceeding maximum time.

The sniper job starts when raising event JOB_OVER_MAX_DUR (event_condition => 'tab.user_data.event_type = ''JOB_OVER_MAX_DUR''').

It runs well on two databases without any issue.

On a third database JOB_OVER_MAX_DUR will be raised a few seconds after starting the JOB for unknown reasons.

The sniper job stops and restarts the job, even if max duration (e.g. one hour) is did not exceed yet.

 

Does anybody has a clue why?

 

 

Here's the code:

-- JOB to be observed

begin

DBMS_SCHEDULER.CREATE_JOB (  job_name => 'Every_1_Minute',

                                job_type => 'stored_procedure',

                                job_action => 'PA_JOB.JOB_EveryMinute',

                                repeat_interval => 'FREQ=MINUTELY;INTERVAL=1;BYSECOND=30',

                                auto_drop => FALSE,

                                comments => 'Every Minute',

                                enabled => TRUE);

DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'Every_1_Minute' , attribute => 'max_run_duration' , value => interval '1' hour);

commit;

end;

 

 

-- sniper JOB observing other jobs

BEGIN

--###### add an event queue subscriber for this user's messages

dbms_scheduler.add_event_queue_subscriber(subscriber_name=>'myagent');

--###### create a program to be able to use metadata argument

dbms_scheduler.create_program (program_name         => 'restarter_program',

                                    program_action      => 'PA_JOB.JOB_Restarter',

                                    program_type        => 'stored_procedure',

                                    number_of_arguments => 1,

                                    enabled             => FALSE) ;

dbms_scheduler.define_metadata_argument ( 'restarter_program','event_message',1);

dbms_scheduler.enable('restarter_program');

--###### create event triggered job

dbms_scheduler.create_job(job_name           => 'JOB_restarter',

                                program_name    => 'restarter_program',

                                event_condition => 'tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',

                                queue_spec      => 'sys.scheduler$_event_queue,myagent',

                                comments        => 'Job triggered by exception JOB_OVER_MAX_DUR. Exception will be raised, if a job exceeds max_run_duration. In this case job will be stoped and restarted (see procedure PA_JOB.JOB_Restarter).',

                                enabled         => TRUE);

DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'JOB_restarter' , attribute => 'job_priority' , value => 1);

commit;

END;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points