Section 8.3. Managing the Calendar and Schedule
8.3. Managing the Calendar and ScheduleIn 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 schedule, 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 StringsThe 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 follows:
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: FREQ=DAILY; BYHOUR=10,14,20 What if I want to run the job every other 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 hour counting from the end of the day. FREQ=DAILY; BYHOUR=-2 Table 8-1 shows some key interval commands for the repeat_interval parameter.
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: FREQ=YEARLY; BYYEARDAY=-1 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. 8.3.1.1. Examples of calendar stringsLet'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.
8.3.1.2. Determining future calendar stringsBecause calendar strings are English-like, they are easy to write. However, it's possible to get confused and end up with a schedule that isn't quite what you meant it to be. The EVALUATE_CALENDAR_STRING procedure available in DBMS_SCHEDULER
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:
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 */ 1 DECLARE 2 l_start_date TIMESTAMP; 3 l_next_date TIMESTAMP; 4 l_return_date TIMESTAMP; 5 BEGIN 6 l_start_date := TRUNC (SYSTIMESTAMP); 7 l_return_date := l_start_date; 8 9 FOR ctr IN 1 .. 10 10 LOOP 11 dbms_scheduler.evaluate_calendar_string ('FREQ=MONTHLY;INTERVAL=2', 12 l_start_date, 13 l_return_date, 14 l_next_date 15 ); 16 DBMS_OUTPUT.put_line ( 'Next Run on: ' 17 || TO_CHAR (l_next_date, 'mm/dd/yyyy hh24:mi:ss') 18 ); 19 l_return_date := l_next_date; 20 END LOOP; 21 END; 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 SchedulesCalendar 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: SQL> SELECT job_name, repeat_interval 2 FROM dba_scheduler_jobs; 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 tedious experience, and it will also be very prone to error. These are the drawbacks to any hardcoding inside an application. The Scheduler gives you another option that helps you avoid this hardcoding. It allows you to create a named 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. 1 BEGIN 2 DBMS_SCHEDULER.create_schedule 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' 7 ); 8 END; Let's examine the lines in detail in the following table.
Once I have created the schedule, my job creation script can simply reference it by name. To illustrate, I'll drop the jobs I have created and start over. BEGIN DBMS_SCHEDULER.drop_job (job_name => 'tabstat_savings'); END; 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. 1 BEGIN 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' 8 ); 9* END; 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. SQL> SELECT job_name, repeat_interval, schedule_name, schedule_owner, schedule_type 2 FROM dba_scheduler_jobs; 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 ScheduleAnother important column in the output from the DBA_SCHEDULER_JOBS view is SCHEDULE_OWNER. In this case, it shows ARUP, the user that created the schedule. The implication 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:
My job creation code now looks like this. 1 BEGIN 2 DMS_SCHEDULER.create_job 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' 9 ); 10* END; 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.
|
