0 Replies Latest reply on Jun 27, 2013 7:54 AM by ma12a02

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


      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


      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);





      -- sniper JOB observing other jobs


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


      --###### 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);


      --###### 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);