msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

25+ Unique Ways to Schedule a Job Using the Oracle Scheduler

One of the powerful features of the Oracle Scheduler is its calendaring syntax. This syntax is used whenever you want to define a recurring job or create a named schedule. You control when and how often a job repeats by setting the repeat_interval attribute of the job itself or of the named schedule that the job references. The result of evaluating the repeat_interval is a set of timestamps. The Scheduler runs the job at each timestamp.

Here are some repeat_interval examples that demonstrate the versatility and flexibility of the calendaring syntax:

Run at 10:00 pm daily from Monday to Friday:
FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=22; BYMINUTE=0; BYSECOND=0;

Run every hour:
FREQ=HOURLY;INTERVAL=1;

Run every 5 minutes:
FREQ=MINUTELY;INTERVAL=5;

Run every Friday at 9:00 am (All three examples are equivalent):
FREQ=DAILY; BYDAY=FRI; BYHOUR=9; BYMINUTE=0; BYSECOND=0;
FREQ=WEEKLY; BYDAY=FRI; BYHOUR=9; BYMINUTE=0; BYSECOND=0;
FREQ=YEARLY; BYDAY=FRI; BYHOUR=9; BYMINUTE=0; BYSECOND=0;

Run every other Friday:
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;

Run on Monday of weeks 5, 10 and 15 every year:
FREQ=YEARLY; BYWEEKNO=5,10,15; BYDAY=MON

Run on the last day of every month.
FREQ=MONTHLY; BYMONTHDAY=-1;

Run on the next to last day of every month:
FREQ=MONTHLY; BYMONTHDAY=-2;

Run on March 10th (Both examples are equivalent):
FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;
FREQ=YEARLY; BYDATE=0310;

Run every January 10, 11, 12, 13 and 14 (Both examples are equivalent):
FREQ=YEARLY; BYDATE=0110,0111,0112,0113,0114
FREQ=YEARLY; BYDATE=0110+SPAN:5D;

Run every 10 days:
FREQ=DAILY; INTERVAL=10;

Run daily at 4:15, 5:15, and 6:15PM:
FREQ=DAILY; BYHOUR=16,17,18; BYMINUTE=15; BYSECOND=0;

Run on the 15th day of every other month:
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;

Run on the 29th day of every month:
FREQ=MONTHLY; BYMONTHDAY=29;

Run on the second Wednesday of each month:
FREQ=MONTHLY; BYDAY=2WED;

Run on the last Friday of the year:
FREQ=YEARLY; BYDAY=-1FRI;

Run every 50 hours:
FREQ=HOURLY; INTERVAL=50;

Run on the last day of every other month:
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=-1;

Run hourly for the first three days of every month:
FREQ=HOURLY; BYMONTHDAY=1,2,3;

Run on the 60th, 120th and 180th days of the year:
FREQ=YEARLY; BYYEARDAY=60,120,180;

Run on the last workday of every month (assuming that workdays are Monday through Friday):
FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1

Here are some more complex examples which reference the following named schedules:

BEGIN
   sys.DBMS_SCHEDULER.create_schedule (
      schedule_name => 'COMPANY_HOLIDAYS',
      repeat_interval => 'FREQ=YEARLY; BYDATE=0704,0905,1124,1125,1225;');
   sys.DBMS_SCHEDULER.create_schedule (
      schedule_name => 'JUL4',
      repeat_interval => 'FREQ=YEARLY; BYMONTH=JUL; BYMONTHDAY=4;');
   sys.DBMS_SCHEDULER.create_schedule (
      schedule_name => 'MEM',
      repeat_interval => 'FREQ=YEARLY; BYMONTH=MAY; BYMONTHDAY=30;');
   sys.DBMS_SCHEDULER.create_schedule (
      schedule_name => 'LAB',
      repeat_interval => 'FREQ=YEARLY; BYMONTH=SEP; BYMONTHDAY=5;');
   sys.DBMS_SCHEDULER.create_schedule (
      schedule_name => 'LAST_SAT',
      repeat_interval => 'FREQ=MONTHLY; BYDAY=SAT; BYSETPOS=-1;');
   sys.DBMS_SCHEDULER.create_schedule (
      schedule_name => 'END_QTR',
      repeat_interval => 'FREQ=YEARLY; BYDATE=0331,0630,0930,1231;');
   sys.DBMS_SCHEDULER.create_schedule (
    schedule_name => 'FISCAL_YEAR',
    repeat_interval => 'FREQ=YEARLY;BYDATE=0301,0601,0901,1201;PERIODS=4;');
END;
/

Run on the last workday of every month, excluding company holidays:
FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; EXCLUDE=COMPANY_HOLIDAYS; BYSETPOS=-1

Run at noon every Friday and on company holidays:
FREQ=YEARLY; BYDAY=FRI;BYHOUR=12; INCLUDE=COMPANY_HOLIDAYS

Run on these three holidays: July 4th, Memorial Day, and Labor Day:
JUL4,MEM,LAB

Run on the last day of the month that is either a Saturday or last day of a quarter:
FREQ=MONTHLY; BYMONTHDAY=-1; INTERSECT=LAST_SAT,END_QTR

Run on the last Wednesday of every quarter of the fiscal year:
FREQ=FISCAL_YEAR;BYDAY=-1WED

Run on the last work day of the 2nd and 4th quarters of the fiscal year (assuming that workdays are Monday through Friday):
FREQ=FISCAL_YEAR;BYDAY=MON,TUE,WED,THU,FRI;BYPERIOD=2,4;BYSETPOS=-1

When constructing your calendar expression it is important to note the following about start dates and repeat intervals (via the documentation):

The Scheduler retrieves the date and time from the start date of the job or named schedule and incorporates them as defaults into the repeat_interval. For example, if the specified frequency is yearly and there is no BYMONTH or BYMONTHDAY clause in the repeat interval, then the month and day that the job runs on are retrieved from the start date. Similarly, if frequency is monthly but there is no BYMONTHDAY clause in the repeat interval, then the day of the month that the job runs on is retrieved from the start date. BYHOUR, BYMINUTE, and BYSECOND defaults are also retrieved from the start date, and used if those clauses are not specified. For example:

start_date: 4/15/05 9:20:00 repeat_interval: freq=yearly

is expanded internally to:

FREQ=YEARLY;BYMONTH=4;BYMONTHDAY=15;BYHOUR=9;BYMINUTE=20;BYSECOND=0

To test the definition of the calendar string without having to actually schedule the job use the very handy procedure DBMS_SCHEDULER.evaluate_calendar_string. This procedure evaluates the calendar expression and tells you what the next execution date and time of a job will be. Consult my previous post or this forum post for examples of how to use evaluate_calendar_string.

By the way, for a creative use of the evaluate_calendar_string procedure check David Aldridge’s post: Generating Lists of Dates in Oracle the DBMS_Scheduler Way.

Happy scheduling!


Filed in Oracle on 02 Feb 11 | Tags: ,


Comments are closed.