dbms_scheduler repeat_interval tip with create_schedule

With blog post, I want to share information about "dbms_scheduler.create_schedule", little less known but very powerful feature of dbms_scheduler package. Lets directly discuss the problem

You want to create a job using dbms_scheduler which should run at following timings daily
13:15
14:30
15:45

So you created following schedule,

SQL> BEGIN
  2    DBMS_SCHEDULER.create_job (
  3       job_name        => 'MY_TEST_JOB',
  4       job_type        => 'PLSQL_BLOCK',
  5       job_action      => 'BEGIN NULL; END;',
  6       start_date      => SYSTIMESTAMP,
  7       repeat_interval => 'FREQ=daily;BYHOUR=13,14,15;BYMINUTE=15,30,45',
  8       end_date        => NULL,
  9       enabled         => TRUE,
 10       comments        => 'Test Job');
 11  END;
 12  /
PL/SQL procedure successfully completed.

But when you looked next day about by executions of job, you found that it actually got executed 9 times instead of 3 times as you wanted.

SQL> SELECT JOB_NAME, TO_CHAR(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI') ACTUAL_START_DATE
  2  FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'MY_TEST_JOB';

JOB_NAME                       ACTUAL_START_DATE
------------------------------ --------------------------
MY_TEST_JOB                    02-APR-2016 13:15
MY_TEST_JOB                    02-APR-2016 13:30
MY_TEST_JOB                    02-APR-2016 13:45
MY_TEST_JOB                    02-APR-2016 14:15
MY_TEST_JOB                    02-APR-2016 14:30
MY_TEST_JOB                    02-APR-2016 14:45
MY_TEST_JOB                    02-APR-2016 15:15
MY_TEST_JOB                    02-APR-2016 15:30
MY_TEST_JOB                    02-APR-2016 15:45

OOPS!!! What is wrong? REPEAT_INTERVAL !!! When you define byminute multiple times with byhour, job is scheduled to run each hour specified in byhour with every combination of byminute. Now what you can do?

One simple solution is to create 3 different jobs with same job action with following repeat_interval:
1. repeat_interval => 'FREQ=daily;BYHOUR=13;BYMINUTE=15'
2. repeat_interval => 'FREQ=daily;BYHOUR=14;BYMINUTE=30'
3. repeat_interval => 'FREQ=daily;BYHOUR=15;BYMINUTE=45'
But it will actually create 3 jobs, you will always need to monitor 3 jobs, which will add little complexity in overall job monitoring and maintenance.

Another better solution is to create 3 schedules and assign them to your job:

SQL> BEGIN
  2    dbms_scheduler.create_schedule('my_test_job_sched1', repeat_interval => 'FREQ=daily;BYHOUR=13;BYMINUTE=15');
  3    dbms_scheduler.create_schedule('my_test_job_sched2', repeat_interval => 'FREQ=daily;BYHOUR=14;BYMINUTE=30');
  4    dbms_scheduler.create_schedule('my_test_job_sched3', repeat_interval => 'FREQ=daily;BYHOUR=15;BYMINUTE=45');
  5  END;
  6  /
PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_SCHEDULER.create_job (
  3       job_name        => 'MY_TEST_JOB',
  4       job_type        => 'PLSQL_BLOCK',
  5       job_action      => 'BEGIN NULL; END;',
  6       start_date      => SYSTIMESTAMP,
  7       repeat_interval => 'my_test_job_sched1,my_test_job_sched2,my_test_job_sched3',
  8       end_date        => NULL,
  9       enabled         => TRUE,
 10       comments        => 'Test Job');
 11  END;
 12  /
PL/SQL procedure successfully completed.

You can also use dbms_scheduler.set_attribute to alter your repeat_interval to use schedules
BEGIN
  dbms_scheduler.set_attribute( name => 'MY_TEST_JOB', attribute => 'repeat_interval', value => 'my_test_job_sched1,my_test_job_sched2,my_test_job_sched3');  
END;  
/


By using multiple schedules in repeat_interval of your job, now you do not need to worry about 3 multiple jobs doing same task. Monitoring and Maintenance of database scheduler are easy (with this scenario) when we use dbms_scheduler.create_schedule.

I hope this article would be helpful to you. Please do post your feedback.

Related Posts:
- One Time Immediate Job in Oracle
- SQL Interview Question Answers
- Oracle Database 12c New Features for Developers
- Oracle SQL Puzzles
- Oracle SQL and PL/SQL Optimization Tips

7 comments:

  1. Hi Nimish,
    just few days ago Connor McDonald (AskTom) wrote on his blog about the utility DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING. Did you read it ?
    A.

    https://connormcdonald.wordpress.com/2016/04/01/understanding-scheduler-syntax/

    ReplyDelete
    Replies
    1. Thank you for sharing. I do follow Connor McDonald blog :)

      Delete
  2. Dilferouz ShaikhApril 6, 2016 at 2:43 PM

    Good info

    ReplyDelete
  3. Hi,
    Nice Post. Thanks for ur Post.

    ReplyDelete
  4. Good information.
    I have one question I have scheduled a job which will run at each 5 Minutes. But duration of task performed in job may take more than 5 minutes. In this case whether next instance of job will be launched even previously scheduled instance is still running?

    ReplyDelete
    Replies
    1. I am not sure, but it should not start executing and should be queued

      Delete