8.3. Managing the Calendar and Schedule
In the previous section, I focused on the first basic component of scheduling: the job. In this section, I'll explore the second key component (and the most visible one), the
, which is the definition of times when a job is supposed to run. You can specify the schedule when you create a job with the CREATE_JOB procedure either by entering a calendar string as the value of the repeat_interval parameter or by referencing a named schedule that you have already created.
8.3.1. Calendar Strings
The syntax of the calendar string you can specify in CREATE_JOB's repeat_interval parameter is quite simple and English-like. For example, if you want to schedule a job that runs Monday through Friday at exactly 7 A.M. and 3 P.M., you would specify the following calendar string as the value for repeat_interval:
FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15
The calendar string has two distinct types of clauses, FREQ and BY..., separated by semicolons. You specify only one FREQ clause , but you may specify several different types of BY clauses, depending on your calendar. Each clause consists of a keyword and a value, separated by an equal sign. Together, the clauses specify how often a job is expected to run, as
Unit of frequency (FREQ)
The FREQ clause specifies the repetition unit. In the example, I want the job to be repeated every day, so I specify FREQ = DAILY. Valid keywords are YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.
The BY clause limits exactly when the job will execute. In the example, I start by specifying the unit of frequency as every day (FREQ=DAILY). I then limit execution to only specific days (Monday, Tuesday, Wednesday, Thursday, and Friday), by specifying BYDAY=MON,TUE,WED,THU,FRI. I then further limit execution by requesting that the job be run at 7 A.M. and 3 P.M., by specifying BYHOUR=7,15. Valid keywords are BYMONTH, BYMONTHDAY, BYYEARDAY, BYHOUR, BYMINUTE, and BYSECOND. These are described in Table 8-1.
Suppose that my job needs to be run every day, regardless of the day of the week, at 10 A.M., 2 P.M., and 8 P.M.; I could specify the repeat_interval parameter as:
What if I want to run the job every
day, not every day? A new keyword, INTERVAL, takes care of that:
FREQ=DAILY; INTERVAL=2, BYHOUR=10,14,20
Here, the INTERVAL=2 clause changes the frequency to 2 units of what is defined in the FREQ clause . Because the FREQ keyword is set to DAILY, the job executes every two days.
A negative number indicates counting from the end of the period. For example, the following indicates the second
counting from the end of the day.
Table 8-1 shows some key interval commands for the repeat_interval parameter.
Table 8-1. BY keywords for calendar strings in the repeat_interval parameter
Schedules the job to run in certain months. For example, to schedule an interest calculation job to run in June and December only, specify:
Alternatively, you can specify numeric month
The exact date of the month when the job will execute is the date when it started. For example, if the job started on the 4th of July, then it will execute every June and December on the 4th. If you want to schedule it on a different date, use the BYMONTHDAY keyword instead.
Specifies exactly which day of the month the job should execute. For example, to execute the job on the 1st day of every month, at 3:00 P.M., specify:
FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=15
If you omit the BYHOUR clause, the job defaults to midnight of the day.
Specifies the day of the year when the job should run. For example, to execute a job on the 15th day of every year, specify:
Schedules the job to run at certain hours. For example, to execute a job every day at 3 A.M., 6 A.M., and 9 A.M., specify:
A schedule based on the above calendar string will be:
... and so on.
A variation on the previous example is the following calendar string:
An interval of MINUTELY means that the job will execute every minute. A schedule based on the above is:
... and so on till 03:59:00. The series repeats at 6:00:00 A.M. and goes on till 06:59:00 A.M.
Schedules a job at a certain minute of the day. To execute a job every 30th minute, specify the calendar string:
A schedule based on this calendar string is:
... and so on.
If you specify FREQ=HOURLY instead of MINUTELY above, the effect will be the same, because the hour is still omitted in this case. However, if the above interval is specified as:
then rounding will be done at the day level. A schedule based on this calendar is:
... and so on.
Here, the job executes at 00:30 hours of every day, because the FREQ clause shows DAILY.
Similar to BYMINUTE and BYHOUR, BYSECOND executes schedules on the specified seconds.
If you specify a negative number as a value for any of these keywords, counting will start from the end instead of from the beginning. For example, the calendar string:
indicates the first day of the year from the end of the year. So the above calendar string will execute the job on December 31st of each year.
18.104.22.168. Examples of calendar strings
Let's look at some additional examples of calendar strings. Suppose that all of these are first specified on jobs starting on July 5th, 2005. I will show you how to specify this calendar string starting with larger intervals and then specifying more granular ones.
Because the frequency is specified as yearly, the schedule is once a year. I have not specified any dates, so the schedule defaults to July 5th every year starting with 2006. This job will execute every year on July 5th at midnight.
Because the INTERVAL clause is specified, the schedule will be set for every other year. This job will execute on July 5th in 2007, 2009, 2011, and so on.
Because I specified a BYMONTH clause, the yearly execution will not default to this month (July), but because I have not specified any dates, it will default to today's date (the 5th). This job will execute every year on January 5thagain, at midnight.
FREQ=YEARLY; BYMONTH=JAN; BYMONTHDAY=2
Note the additional clause BYMONTHDAY. This indicates that the schedule will be operational on the 2nd day of the month, not the default. This job will execute on January 2nd 2006, January 2nd 2007, and so on.
FREQ=YEARLY; BYMONTH=JAN; BYDAY=SUN
Here, instead of specifying a date, I have specified a day of the week in that month. This job will execute on every Sunday in January of every year. Some example schedules, in mm/dd/yyyy format, are:
All these are Sundays.
FREQ=YEARLY; BYMONTH=JAN; BYMONTHDAY=2; BYDAY=SUN
This is a
twist on the previous two schedules. The clause BYMONTHDAY specifies a specific day of the month (2nd), but the BYDAY clause specifies a specific day of the week (Sunday). Hence, this job will execute every Sunday in January when the date is the 2nd. The schedule is:
Note that the first occurrence is on January 2nd, 2011, because that happens to be a Sunday.
Here I specify every 60th day of the year. The schedule is:
Note how the Scheduler takes care of the leap year, 2008, where the 60th day
on February 29th. On all other
the 60th day of the year is March 1st.
FREQ=YEARLY; BYYEARDAY=60; BYMONTHDAY=1
By combining conditions, I can get the specific results I need. In this case I specify the 60th day of the year but on the 1st of the month. The schedule is:
Note that the year 2008 is not included. On that year, the 60th day falls on February 29th, not on the first of any month, so it is not included in the schedule.
that the schedule should be repeated every day for the second week of the year. The schedule is:
Note how all seven days of the second week of all of the years show up in the schedule.
This job executes every day at 3 A.M., 6 A.M., and 9 A.M., exactly on the hour.
...and so on.
FREQ=DAILY; INTERVAL=2; BYHOUR=3,6,9
This specification is the same as the previous one, except that the INTERVAL is set to 2. This means that the actual interval between two executions should be two times the interval specified in the FREQ clausethat is, two days.
... and so on.
Note how it executes on July 6th and then on July 8th.
FREQ=DAILY; BYHOUR=3,6,9; BYMINUTE=00,15,30,45
This job executes every day at 3 A.M., 6 A.M., and 9 A.M. as above, but because the BYMINUTE clause is specified, the execution occurs at those minute markers. So the schedule is:
... and so on
22.214.171.124. Determining future calendar strings
Because calendar strings are English-like, they are easy to write. However, it's possible to get
and end up with a schedule that isn't quite what you
it to be. The EVALUATE_CALENDAR_STRING procedure available in DBMS_SCHEDULER
When BYWEEKNO is specified, the Scheduler uses week numbering as specified by the ISO-8601 standard , which introduces its own idiosyncrasies. Here is what you need to know about ISO-8601 week numbering:
The first day of the week is
to be Monday, not Sunday, because this is common practice in many businesses and programs.
Week numbers may be from 1 to 53.
The first week starts from the first Monday of the year; hence, there may be some days before the first week of the year. For example, in 2005 if your scheduling calendar specifies "FREQ=YEARLY; BYWEEKNO=1", the first day this schedule will start is January 2nd, 2005, because that is the first Monday of the year 2005. This means that the date January 1st will never appear in any of the schedules. This may not present a problem for you, but you should be aware of it.
Similarly, the last week of the year may not cover all of the year. Part of the year may be left out, and will be included in the first week of the
The last week of the year may contain some
from the next year. Consider the calendar string "FREQ=YEARLY; BYWEEKNO=52". When specified in 2005, it shows the following schedule:
... and so on
Note that the last week of the year includes January 1st, 2006, which is in 2006, not 2005. Even though it shows up as the 52nd week of the year 2005, the schedule actually goes into 2006. If you aren't aware of this important fact when you build your schedule, the outcome may be very different from what you anticipate.
will examine your calendar string and produce a sample schedule that you can scrutinize to make sure that your job will execute as you expect it to.
The procedure accepts four parameters:
Calendar string to be evaluated
Date (specified as a TIMESTAMP datatype) where you want to start
If you need to specify dates and times that fall after a certain date, use this parameter to start the sequence of scheduled dates after that date.
This is an OUT parameter. The procedure places the date and time when the Scheduler will execute this calendar string here.
Let's look at an example of using this procedure to get the execution schedules for the calendar string "FREQ=MONTHLY;INTERVAL=2".
/* File on web: cal_eval.sql */
2 l_start_date TIMESTAMP;
3 l_next_date TIMESTAMP;
4 l_return_date TIMESTAMP;
6 l_start_date := TRUNC (SYSTIMESTAMP);
7 l_return_date := l_start_date;
9 FOR ctr IN 1 .. 10
11 dbms_scheduler.evaluate_calendar_string ('FREQ=MONTHLY;INTERVAL=2',
16 DBMS_OUTPUT.put_line ( 'Next Run on: '
17 TO_CHAR (l_next_date, 'mm/dd/yyyy hh24:mi:ss')
19 l_return_date := l_next_date;
20 END LOOP;
The output is:
Next Run on: 09/06/2005 00:00:00
Next Run on: 11/06/2005 00:00:00
Next Run on: 01/06/2006 00:00:00
Next Run on: 03/06/2006 00:00:00
Next Run on: 05/06/2006 00:00:00
Next Run on: 07/06/2006 00:00:00
Next Run on: 09/06/2006 00:00:00
Next Run on: 11/06/2006 00:00:00
Next Run on: 01/06/2007 00:00:00
Next Run on: 03/06/2007 00:00:00
You can check over the exact dates and times of future executions to make sure they are what you expect them to be.
8.3.2. Named Schedules
Calendar strings provide a very helpful tool for specifying the schedule for executing your jobs. But suppose you have several jobs that run at the same timefor example, you might be collecting optimizer statistics for several tables. Here is an excerpt from the data dictionary view showing the schedule for these jobs:
SELECT job_name, repeat_interval
TABSTAT_ACCOUNTS FREQ=DAILY; BYHOUR=3
TABSTAT_SAVINGS FREQ=DAILY; BYHOUR=3
TABSTAT_CHECKING FREQ=DAILY; BYHOUR=3
... and so on ...
Notice that the jobs have all the same calendar string, "FREQ=DAILY; BYHOUR=3", which indicates execution at 3:00 A.M. every day. Now, suppose you want to change the timing to 4:00 A.M. instead of 3:00; what will you have to do?
You will have to go through each job painstakingly and change the calendar string. The more jobs you have, the more work you have to doit will be a very
experience, and it will also be very prone to error. These are the drawbacks to any
inside an application.
The Scheduler gives you another option that helps you avoid this hardcoding. It allows you to create a
schedule, which all of your jobs can reference. If you use a named schedule, you won't need to explicitly state the calendar string; maintenance of your schedules will be much simpler. Let's see how this works.
Using the CREATE_SCHEDULE procedure , I can create a schedule called opt_stat_coll_sched, which specifies the calendar string I defined above.
3 (schedule_name => 'opt_stat_coll_sched',
4 start_date => SYSTIMESTAMP,
5 repeat_interval => 'FREQ=DAILY; BYHOUR=3',
6 comments => 'Run daily at 3 AM'
Let's examine the lines in detail in the following table.
of the schedule.
Time the named schedule should start. In this example, I define it to start now. The time is in the TIMESTAMP datatype, so I specify SYSTIMESTAMP instead of SYSDATE.
Calendar string that defines the schedule.
Comments that describe the schedule.
Once I have created the schedule, my job creation script can simply reference it by name. To
, I'll drop the jobs I have created and start over.
DBMS_SCHEDULER.drop_job (job_name => 'tabstat_savings');
Similarly, I'll drop all of my jobs and then re-create them using the named schedule. Here is one example for a job named TABSTAT_SAVINGS.
2 DBMS_SCHEDULER.create_job (job_name => 'tabstat_savings',
3 job_type => 'stored_procedure',
4 job_action => 'collect_stats_checking',
5 schedule_name => 'opt_stat_coll_sched'
6 enabled => TRUE,
7 comments => 'Collect Optimizer Stats'
This code is almost identical to the previous job creation code except for line 5, which shows a new parameterschedule_name, where I can reference the named schedule I just created.
To check a schedule for a job, I can query the DBA_SCHEDULER_JOBS view as before. Assume that I have just re-created the job TABSTAT_SAVINGS to use a named schedule. The other two jobs are still on a calendar.
SELECT job_name, repeat_interval, schedule_name, schedule_owner, schedule_type
JOB_NAME REPEAT_INTERVAL SCHEDULE_NAME SCHEDU SCHEDULE_
-------------------- -------------------- -------------------- ------ ---------
TABSTAT_ACCOUNTS FREQ=DAILY; BYHOUR=3 CALENDAR
TABSTAT_SAVINGS OPT_STAT_COLL_SCHED ARUP NAMED
TABSTAT_CHECKING FREQ=DAILY; BYHOUR=3 CALENDAR
Note the SCHEDULE_TYPE column , which shows NAMED for the job I just turned over to a named schedule. That column shows CALENDAR for the jobs still using the calendar string as schedules. The column SCHEDULE_NAME shows the name of the schedule you just assigned to the job. Also, when I provide a named schedule for a job, the column REPEAT_INTERVAL becomes NULL for that job.
8.3.3. Owner of the Schedule
Another important column in the output from the DBA_SCHEDULER_JOBS view is SCHEDULE_OWNER. In this case, it shows ARUP, the
that created the schedule. The
of this column is significant: a schedule need not be created by the user who actually runs a job; it could be defined by another user. Note, however, that the user who creates a schedule must have the CREATE JOB system privilege . Because the schedule is created using the DBMS_SCHEDULER package , that user must also have the EXECUTE privilege on the package.
Suppose that I am creating a single user named SCHED_MANAGER, to manage all of the schedules for your database. Doing so will make it easier to manage schedules and will establish a single point of control for these schedules. As I noted above, that user must be given the following privileges:
EXECUTE ON DBMS_SCHEDULER
My job creation code now looks like this.
3 (job_name => 'tabstat_savings',
4 job_type => 'stored_procedure',
5 job_action => 'collect_stats_checking',
6 schedule_name => 'SCHED_MANAGER.opt_stat_coll_sched'
7 enabled => TRUE,
8 comments => 'Collect SAVINGS Stats'
Note that line 6 has changed. Now the schedule_name parameter shows SCHED_MANAGER.opt_stat_coll_sched, which indicates the owner of the schedule. In my earlier examples, I did not include a prefix for the schedule name; if you omit the prefix, the default is that the schedule belongs to the user who is creating the job.
Once a schedule has been created by one user, it can be used by
other user. There is no fine-grained access control that may be used to control access to a specific schedule. Keep this mind when you are creating your schedules.