How repeat_interval works
repeat_interval uses Oracle's calendaring syntax: clauses separated by semicolons, starting with FREQ. The frequency sets the cycle; every BY clause narrows the days and times inside each cycle. FREQ=WEEKLY;BYDAY=MON,FRI means "each week, on Monday and Friday".
Anything you leave out is taken from the job's start_date. FREQ=DAILY on its own runs at the start date's time of day; FREQ=MONTHLY runs on the start date's day of the month. That is the most common surprise: a job whose start date is 14:37:22 with FREQ=DAILY;BYHOUR=2 runs at 02:37:22 — unless you also give BYMINUTE=0;BYSECOND=0. The builder always writes them out.
The same applies to short intervals. FREQ=MINUTELY;INTERVAL=10 counts from the start time, so a job started at 14:37:22 runs at 14:47:22, 14:57:22 and so on. For runs on the clock — :00, :10, :20 — list the minutes instead: FREQ=HOURLY;BYMINUTE=0,10,20,30,40,50;BYSECOND=0. The builder does this whenever the interval divides an hour evenly.
INTERVAL counts from the start date
INTERVAL=2 means every second cycle, counted from start_date — not from today, and not from the 1st of the month. For "every other Friday", the start date decides which Fridays: start the job in the right week. Change the "from" date in the tool to see the effect.
Clause reference
| Clause | Values | What it does |
|---|---|---|
FREQ | YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, SECONDLY | Required. The base cycle. |
INTERVAL | 1–99 | Run every n-th cycle: INTERVAL=2 with WEEKLY is every other week. Counted from the start date. |
BYMONTH | 1–12 or JAN–DEC | Only these months. |
BYMONTHDAY | 1–31, or -1 for the last day | Days of the month. -1 is the last day, -2 the day before. |
BYDAY | MON–SUN, optionally numbered | Days of the week. In MONTHLY rules, 1MON is the first Monday and -1FRI the last Friday; in YEARLY rules the number counts within the year. |
BYHOUR / BYMINUTE / BYSECOND | 0–23 / 0–59 / 0–59 | Times of day. Leave one out and the start date fills it in. |
BYYEARDAY | 1–366, or negative | Days of the year. |
BYWEEKNO | 1–53, or negative | ISO week numbers (weeks start on Monday). |
BYSETPOS | position in each cycle | Picks the n-th match of each MONTHLY or YEARLY cycle — e.g. the last weekday. Not allowed with WEEKLY, DAILY or shorter cycles (ORA-27424). |
Mistakes that make jobs run at the wrong time
- Forgetting BYMINUTE and BYSECOND. The missing parts come from
start_date, so the job drifts to odd minutes and seconds. - Expecting BYHOUR=9,17 with BYMINUTE=0,30 to give two runs. Clauses combine as a grid: that is four runs (09:00, 09:30, 17:00, 17:30). For different minutes at different hours, use two jobs or a named schedule.
- Using BYSETPOS with FREQ=WEEKLY or DAILY. Oracle rejects it with ORA-27424; BYSETPOS only works with MONTHLY and YEARLY.
- A rule that can never run.
FREQ=MONTHLY;BYMONTHDAY=31;BYMONTH=2has no valid date, so Oracle raises ORA-27419 when the job is enabled. The tool shows the same error. - Daylight saving. Give
start_dateas aTIMESTAMP WITH TIME ZONEwith a region (for example'Asia/Karachi'or'Europe/London') so runs follow local time across DST changes. This tool shows wall-clock times without a time zone.
Test a schedule in your own database
Oracle can list the next run dates itself, without creating a job:
DECLARE
l_next TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
BEGIN
FOR i IN 1 .. 10 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
calendar_string => 'FREQ=WEEKLY;BYDAY=MON,WED,FRI;BYHOUR=9;BYMINUTE=0;BYSECOND=0',
start_date => SYSTIMESTAMP,
return_date_after => l_next,
next_run_date => l_next);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_next, 'Dy DD-Mon-YYYY HH24:MI:SS'));
END LOOP;
END;
/
This is exactly how the builder was tested: its results were compared with EVALUATE_CALENDAR_STRING on Oracle AI Database 26ai for over a thousand schedules, including every example on this page. For very unusual combinations of clauses, confirm with the block above.
Questions developers ask
How do I run a job every 5 minutes, only during office hours?
FREQ=HOURLY;BYMINUTE=0,5,10,15,20,25,30,35,40,45,50,55;BYSECOND=0;BYHOUR=9,10,11,12,13,14,15,16,17;BYDAY=MON,TUE,WED,THU,FRI. List every hour and minute you want — the calendaring syntax has no ranges — and the runs stay on the clock whatever the job's start time. This one runs from 09:00 to 17:55.
How do I schedule the last working day of the month?
FREQ=MONTHLY;BYDAY=MON,TUE,WED,THU,FRI;BYSETPOS=-1 — take all weekdays of the month and keep the last one. Public holidays need an exclusion schedule.
Can I still use a PL/SQL expression like SYSDATE + 1/24?
Yes, repeat_interval also accepts a PL/SQL date expression, evaluated after each run. But an expression drifts by the job's run time, while calendaring syntax stays on a fixed grid. Prefer calendaring syntax for anything that must run at set times.
Learn more: DBMS_SCHEDULER tutorial · Also on this site: Oracle Date Format Tester