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