0

I need to launch a scheduled job at 12 o'clock at night and keep this job running for a period of 8 hours. What happens now is that the job is running for more than 12 hours and I really need to avoid running for so long. I have used the Max Run Duration option but it does not have the desired effect for my purpose. I would appreciate if someone could give me an example of how it should be. Database is Oracle 12c. Here I put the script:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.POB_TB_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2019/02/10 00:05:00.000000 America/New_York','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'SELLER.FILL_TB_S1'
      ,comments        => NULL
    );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.POB_TB_JOB'
     ,attribute => 'MAX_RUN_DURATION'
     ,value     => TO_DSINTERVAL('+000 05:00:00'));

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.POB_TB_JOB'
     ,attribute => 'number_of_arguments'
     ,value     => 4);

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 1
     ,argument_value       => '30');

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 2
     ,argument_value       => 'N');

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 3
     ,argument_value       => 'N');

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 4
     ,argument_value       => '1');

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'SYS.POB_TB_JOB');
END;
/
2

Attribute MAX_RUN_DURATION does not terminate the job, it only raises an event.

I created a procedure like this:

procedure SchedulerWatchdog(jobName in varchar2) is

cursor Jobs is
select job_name
from user_scheduler_jobs
where JOB_NAME = jobName
   AND STATE = 'RUNNING'
   AND systimestamp - LAST_START_DATE > MAX_RUN_DURATION;

begin
   for aJob in Jobs loop
      DBMS_SCHEDULER.STOP_JOB(JobName, FORCE => TRUE);
   end loop;
end;

Run this procedure by scheduler job, perhaps every Minute.

0

Context:

If what you need is for the job to continue running until 8 hours have passed since 12 p.m., this is the way to tell the job to perform a pause type until 8 a.m.

PROCEDURE SELLER.FILL_TB_S1 IS
BEGIN
   /* YOUR CODE */
   WHILE TO_CHAR(SYSDATE, 'HH24MI') != 800 LOOP NULL; END LOOP;
END;
  • I think such a loop will heat up your CPU. Instead of NULL; use better DBMS_LOCK.SLEEP(60); or similar. – Wernfried Domscheit Mar 24 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.