| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
A schedule defines when a job should be run or when a window should open. Schedules can be shared among users by creating and saving them as objects in the database.
This section introduces you to basic schedule tasks, and discusses the following topics:
See Also:
"Schedules" for an overview of schedules.Table 27-3 illustrates common schedule tasks and the procedures you use to handle them.
Table 27-3 Schedule Tasks and Their Procedures
| Task | Procedure | Privilege Needed |
|---|---|---|
|
Create a schedule |
|
|
|
Alter a schedule |
|
|
|
Drop a schedule |
|
|
See "Scheduler Privileges" for further information regarding privileges.
You create schedules by using the CREATE_SCHEDULE procedure or Enterprise Manager. Schedules are created in the schema of the user creating the schedule, and are enabled when first created. You can create a schedule in another user's schema. Once a schedule has been created, it can be used by other users. The schedule is created with access to PUBLIC. Therefore, there is no need to explicitly grant access to the schedule. An example of creating a schedule is the following statement:
BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE ( schedule_name => 'my_stats_schedule', start_date => SYSTIMESTAMP, end_date => SYSTIMESTAMP + INTERVAL '30' day, repeat_interval => 'FREQ=HOURLY; INTERVAL=4', comments => 'Every 4 hours'); END; /
See Oracle Database PL/SQL Packages and Types Reference for detailed information about the CREATE_SCHEDULE procedure.
You alter a schedule by using the SET_ATTRIBUTE procedure or Enterprise Manager. Altering a schedule changes the definition of the schedule. With the exception of schedule name, all attributes can be changed. The attributes of a schedule are available in the *_SCHEDULER_SCHEDULES views.
If a schedule is altered, the change will not affect running jobs and open windows that use this schedule. The change will only be in effect the next time the jobs runs or the window opens.
See Oracle Database PL/SQL Packages and Types Reference for detailed information about the SET_ATTRIBUTE procedure.
You drop a schedule using the DROP_SCHEDULE procedure or Enterprise Manager. This procedure call will delete the schedule object from the database.
See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DROP_SCHEDULE procedure.
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. You can set repeat_interval with DBMS_SCHEDULER package procedures or with Enterprise Manager.
The result of evaluating the repeat_interval is a set of timestamps. The Scheduler runs the job at each timestamp. Note that the start date from the job or schedule also helps determine the resulting set of timestamps. (See Oracle Database PL/SQL Packages and Types Reference for more information about repeat_interval evaluation.) If no value for repeat_interval is specified, the job runs only once at the specified start date.
Immediately after a job is started, the repeat_interval is evaluated to determine the next scheduled execution time of the job. It is possible that the next scheduled execution time arrives while the job is still running. A new instance of the job, however, will not be started until the current one completes.
There are two ways to specify the repeat interval:
The primary method of setting how often a job will repeat is by setting the repeat_interval attribute with a Scheduler calendaring expression. See Oracle Database PL/SQL Packages and Types Reference for a detailed description of the calendaring syntax for repeat_interval as well as the CREATE_SCHEDULE procedure.
Examples of Calendaring Expressions
The following examples illustrate simple repeat intervals. For simplicity, it is assumed that there is no contribution to the evaluation results by the start date.
Run every Friday. (All three examples are equivalent.)
FREQ=DAILY; BYDAY=FRI; FREQ=WEEKLY; BYDAY=FRI; FREQ=YEARLY; BYDAY=FRI;
Run every other Friday.
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;
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 10 days.
FREQ=DAILY; INTERVAL=10;
Run daily at 4, 5, and 6PM.
FREQ=DAILY; BYHOUR=16,17,18;
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;
Here are some more complex repeat intervals:
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
Run on the last workday of every month, excluding company holidays. (This example references an existing named schedule called 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. (This example references three existing named schedules—JUL4, MEM, and LAB—where each defines a single date corresponding to a holiday.)
JUL4,MEM,LAB
Examples of Calendaring Expression Evaluation
A repeat interval of "FREQ=MINUTELY;INTERVAL=2;BYHOUR=17; BYMINUTE=2,4,5,50,51,7;" with a start date of 28-FEB-2004 23:00:00 will generate the following schedule:
SUN 29-FEB-2004 17:02:00 SUN 29-FEB-2004 17:04:00 SUN 29-FEB-2004 17:50:00 MON 01-MAR-2004 17:02:00 MON 01-MAR-2004 17:04:00 MON 01-MAR-2004 17:50:00 ...
A repeat interval of "FREQ=MONTHLY;BYMONTHDAY=15,-1" with a start date of 29-DEC-2003 9:00:00 will generate the following schedule:
WED 31-DEC-2003 09:00:00 THU 15-JAN-2004 09:00:00 SAT 31-JAN-2004 09:00:00 SUN 15-FEB-2004 09:00:00 SUN 29-FEB-2004 09:00:00 MON 15-MAR-2004 09:00:00 WED 31-MAR-2004 09:00:00 ...
A repeat interval of "FREQ=MONTHLY;" with a start date of 29-DEC-2003 9:00:00 will generate the following schedule. (Note that because there is no BYMONTHDAY clause, the day of month is retrieved from the start date.)
MON 29-DEC-2003 09:00:00 THU 29-JAN-2004 09:00:00 SUN 29-FEB-2004 09:00:00 MON 29-MAR-2004 09:00:00 ...
Example of Using a Calendaring Expression
As an example of using the calendaring syntax, consider the following statement:
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'scott.my_job1', start_date => '15-JUL-04 01.00.00 AM Europe/Warsaw', repeat_interval => 'FREQ=MINUTELY; INTERVAL=30;', end_date => '15-SEP-04 01.00.00 AM Europe/Warsaw', comments => 'My comments here'); END; /
This creates my_job1 in scott. It will run for the first time on July 15th and then run until September 15. The job is run every 30 minutes.
When you need more complicated capabilities than the calendaring syntax provides, you can use PL/SQL expressions. You cannot, however, use PL/SQL expressions for windows or in named schedules. The PL/SQL expression must evaluate to a date or a timestamp. Other than this restriction, there are no limitations, so with sufficient programming, you can create every possible repeat interval. As an example, consider the following statement:
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'scott.my_job2', start_date => '15-JUL-04 01.00.00 AM Europe/Warsaw', repeat_interval => 'SYSTIMESTAMP + INTERVAL '30' MINUTE', end_date => '15-SEP-04 01.00.00 AM Europe/Warsaw', comments => 'My comments here'); END; /
This creates my_job1 in scott. It will run for the first time on July 15th and then every 30 minutes until September 15. The job is run every 30 minutes because repeat_interval is set to SYSTIMESTAMP + INTERVAL '30' MINUTE, which returns a date 30 minutes into the future.
The following are important differences in behavior between a calendaring expression and PL/SQL repeat interval:
Start date
Using the calendaring syntax, the start date is a reference date only. This means that the schedule is valid as of this date. It does not mean that the job will start on the start date.
Using a PL/SQL expression, the start date represents the actual time that the job will start executing for the first time.
Next run time
Using the calendaring syntax, the next time the job will run is fixed.
Using the PL/SQL expression, the next time the job will run depends on the actual start time of the current run of the job. As an example of the difference, if a job started at 2:00 PM and its schedule was to repeat every 2 hours, then, if the repeat interval was specified with the calendaring syntax, it would repeat at 4, 6 and so on. If PL/SQL was used and the job started at 2:10, then the job would repeat at 4:10, and if the next job actually started at 4:11, then the subsequent run would be at 6:11.
To illustrate these two points, consider a situation where you have a start date of 15-July-2003 1:45:00 and you want it to repeat every two hours. A calendar expression of "FREQ=HOURLY; INTERVAL=2; BYMINUTE=0;" will generate the following schedule:
TUE 15-JUL-2003 03:00:00 TUE 15-JUL-2003 05:00:00 TUE 15-JUL-2003 07:00:00 TUE 15-JUL-2003 09:00:00 TUE 15-JUL-2003 11:00:00 ...
Note that the calendar expression repeats every two hours on the hour.
A PL/SQL expression of "SYSTIMESTAMP + interval '2' hour", however, might have a run time of the following:
TUE 15-JUL-2003 01:45:00 TUE 15-JUL-2003 03:45:05 TUE 15-JUL-2003 05:45:09 TUE 15-JUL-2003 07:45:14 TUE 15-JUL-2003 09:45:20 ...
For repeating jobs, the next time a job is scheduled to run is stored in a timestamp with time zone column. When using the calendaring syntax, the time zone is retrieved from start_date. For more information on what happens when start_date is not specified, see Oracle Database PL/SQL Packages and Types Reference.
In the case of repeat intervals that are based on PL/SQL expressions, the time zone is part of the timestamp that is returned by the PL/SQL expression. In both cases, it is important to use region names. For example, "Europe/Istanbul", instead of absolute time zone offsets such as "+2:00". Only when a time zone is specified as a region name will the Scheduler follow daylight savings adjustments that apply to that region.
