0 Replies Latest reply: Jun 27, 2013 2:54 AM by user11111706 RSS

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

    user11111706

      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;