设置Repeat Interval参数（实例参考）

    Job 和Schedule中REPEAT_INTERVAL参数都是用来控制执行的频率或周期，虽然说周期是一个时间性概念，不过   REPEAT_INTERVAL指定的时候并不是一个时间值，而是由一组关键字描述的时间。 

1. 　　REPEAT_INTERVAL 参数的详细语法如下：  
2.   
3. repeat_interval = regular_schedule | combined_schedule  
4.   
5. ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝  
6.   
7. regular_schedule = frequency_clause  
8.   
9. [";" interval_clause] [";" bymonth_clause] [";" byweekno_clause]  
10.   
11. [";" byyearday_clause] [";" bydate_clause] [";" bymonthday_clause]  
12.   
13. [";" byday_clause] [";" byhour_clause] [";" byminute_clause]  
14.   
15. [";" bysecond_clause] [";" bysetpos_clause] [";" include_clause]  
16.   
17. [";" exclude_clause] [";" intersect_clause][";" periods_clause]  
18.   
19. [";" byperiod_clause]  
20.   
21. ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝    
22.   
23. combined_schedule = schedule_list [";" include_clause]  
24.   
25. [";" exclude_clause] [";" intersect_clause]  
26.   
27. frequency_clause = "FREQ" "=" ( predefined_frequency | user_defined_frequency )  
28.   
29. predefined_frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" |   
30.   
31.    "HOURLY" | "MINUTELY" | "SECONDLY"  
32.   
33. user_defined_frequency = named_schedule  
34.   
35. ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝  
36.   
37. interval_clause = "INTERVAL" "=" intervalnum  
38.   
39.    intervalnum = 1 through 99  
40.   
41. bymonth_clause = "BYMONTH" "=" monthlist  
42.   
43.    monthlist = monthday ( "," monthday)*  
44.   
45.    month = numeric_month | char_month  
46.   
47.    numeric_month = 1 | 2 | 3 ...  12  
48.   
49.    char_month = "JAN" | "FEB" | "MAR" | "APR" | "MAY" | "JUN" |  
50.   
51.    "JUL" | "AUG" | "SEP" | "OCT" | "NOV" | "DEC"  
52.   
53. byweekno_clause = "BYWEEKNO" "=" weeknumber_list  
54.   
55.    weeknumber_list = weeknumber ( "," weeknumber)*  
56.   
57.    weeknumber = [minus] weekno  
58.   
59.    weekno = 1 through 53  
60.   
61. byyearday_clause = "BYYEARDAY" "=" yearday_list  
62.   
63.    yearday_list = yearday ( "," yearday)*  
64.   
65.    yearday = [minus] yeardaynum  
66.   
67.    yeardaynum = 1 through 366  
68.   
69. bydate_clause = "BYDATE" "=" date_list  
70.   
71.    date_list = date ( "," date)*  
72.   
73.    date = [YYYY]MMDD [ offset | span ]  
74.   
75. bymonthday_clause = "BYMONTHDAY" "=" monthday_list  
76.   
77.    monthday_list = monthday ( "," monthday)*  
78.   
79.    monthday = [minus] monthdaynum  
80.   
81.    monthdaynum = 1 through 31  
82.   
83. byday_clause = "BYDAY" "=" byday_list  
84.   
85.    byday_list = byday ( "," byday)*  
86.   
87.    byday = [weekdaynum] day  
88.   
89.    weekdaynum = [minus] daynum  
90.   
91.    daynum = 1 through 53 /* if frequency is yearly */  
92.   
93.    daynum = 1 through 5  /* if frequency is monthly */  
94.   
95.    day = "MON" | "TUE" | "WED" | "THU" | "FRI" | "SAT" | "SUN"  
96.   
97. byhour_clause = "BYHOUR" "=" hour_list  
98.   
99.    hour_list = hour ( "," hour)*  
100.   
101.    hour = 0 through 23  
102.   
103. byminute_clause = "BYMINUTE" "=" minute_list  
104.   
105.    minute_list = minute ( "," minute)*  
106.   
107.    minute = 0 through 59  
108.   
109. bysecond_clause = "BYSECOND" "=" second_list  
110.   
111.    second_list = second ( "," second)*  
112.   
113.    second = 0 through 59  
114.   
115. bysetpos_clause = "BYSETPOS" "=" setpos_list  
116.   
117.    setpos_list = setpos ("," setpos)*  
118.   
119.    setpos = [minus] setpos_num  
120.   
121.    setpos_num = 1 through 9999  
122.   
123. ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝  
124.   
125. include_clause = "INCLUDE" "=" schedule_list  
126.   
127. exclude_clause = "EXCLUDE" "=" schedule_list  
128.   
129. intersect_clause = "INTERSECT" "=" schedule_list  
130.   
131. schedule_list = schedule_clause ("," schedule_clause)*  
132.   
133. schedule_clause = named_schedule [ offset ]  
134.   
135. named_schedule = [schema "."] schedule  
136.   
137. periods_clause = "PERIODS" "=" periodnum  
138.   
139. byperiod_clause = "BYPERIOD" "=" period_list  
140.   
141. period_list = periodnum ("," periodnum)*  
142.   
143. periodnum = 1 through 100  
144.   
145. ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝  
146.   
147. offset = ("+" | "-") ["OFFSET:"] duration_val  
148.   
149. span = ("+" | "-" | "^") "SPAN:" duration_val  
150.   
151. duration_val = dur-weeks | dur_days  
152.   
153. dur_weeks = numofweeks "W"  
154.   
155. dur_days = numofdays "D"  
156.   
157. numofweeks = 1 through 53  
158.   
159. numofdays = 1 through 376  
160.   
161. minus = "-"   
162. 　　
163. 这里不准备逐条解释每一个语法细节，下面将着重通过一些常用设置，希望能够更有助于广大同仁的理解。 
     

    查看执行时间结果脚本
   repeat_interval.sql

declare
        l_start_date timestamp;
        l_next_date timestamp;
        l_return_date timestamp;
        begin
          l_start_date
                := to_timestamp_tz(to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS');
          l_return_date := l_start_date;
        for i in 1..10 loop
             dbms_scheduler.evaluate_calendar_string  ('FREQ=MONTHLY; BYMONTHDAY=-1;',
                           l_start_date,l_return_date,l_next_date);
             dbms_output.put_line('Next Run On:  '||to_char(l_next_date,'yyyy-mm-dd hh24:mi:ss day'));
             l_return_date := l_next_date;
end loop;
end ;
/
EVALUATE_CALENDAR_STRING Procedure

You can define repeat intervals of jobs, windows or schedules using the Scheduler's calendaring syntax. This procedure evaluates the calendar expression and tells you what the next execution date and time of a job or window will be. This is very useful for testing the correct definition of the calendar string without having to actually schedule the job or window.

This procedure can also be used to get multiple steps of the repeat interval by passing the next_run_date returned by one invocation as the return_date_after argument of the next invocation of this procedure.

Syntax

DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
   calendar_string    IN  VARCHAR2,
   start_date         IN  TIMESTAMP WITH TIME ZONE,
   return_date_after  IN  TIMESTAMP WITH TIME ZONE,
   next_run_date      OUT TIMESTAMP WITH TIME ZONE);

Parameters

Table 114-46 EVALUATE_CALENDAR_STRING Procedure Parameters
Parameter     Description

calendar_string
   

The calendar string to be evaluated. The string must be in the calendaring syntax described in "Operational Notes".

start_date
   

The date and time after which the repeat interval becomes valid. It can also be used to fill in specific items that are missing from the calendar string. Can optionally be NULL.

return_date_after
   

With the start_date and the calendar string, the Scheduler has sufficient information to determine all valid execution dates. By setting this argument, the Scheduler knows which one of all possible matches to return. When a NULL value is passed for this argument, the Scheduler automatically fills in systimestamp as its value.

next_run_date
   

The first timestamp that matches the calendar string and start date that occurs after the value passed in for the return_date_after argument.

1.每天的7 8 16 点执行
SYS@standby1/2011-04-28 19:50:37>@repeat_interval.sql
Enter value for repeat_interval: FREQ=DAILY;BYHOUR=16,07,8;
Next Run On:  2011-04-29 07:50:47 friday
Next Run On:  2011-04-29 08:50:47 friday
Next Run On:  2011-04-29 16:50:47 friday
Next Run On:  2011-04-30 07:50:47 saturday
Next Run On:  2011-04-30 08:50:47 saturday
Next Run On:  2011-04-30 16:50:47 saturday
Next Run On:  2011-05-01 07:50:47 sunday
Next Run On:  2011-05-01 08:50:47 sunday
Next Run On:  2011-05-01 16:50:47 sunday
Next Run On:  2011-05-02 07:50:47 monday

PL/SQL procedure successfully completed.
2.每隔一个月的十五号
SYS@standby1/2011-04-28 19:50:47>@repeat_interval.sql
Enter value for repeat_interval: FREQ=MONTHLY;INTERVAL=2;BYMONTHDAY=15;
Next Run On:  2011-06-15 19:52:07 wednesday
Next Run On:  2011-08-15 19:52:07 monday
Next Run On:  2011-10-15 19:52:07 saturday
Next Run On:  2011-12-15 19:52:07 thursday
Next Run On:  2012-02-15 19:52:07 wednesday
Next Run On:  2012-04-15 19:52:07 sunday
Next Run On:  2012-06-15 19:52:07 friday
Next Run On:  2012-08-15 19:52:07 wednesday
Next Run On:  2012-10-15 19:52:07 monday
Next Run On:  2012-12-15 19:52:07 saturday

PL/SQL procedure successfully completed.
3。每年的3月10号
Enter value for repeat_interval: FREQ=YEARLY;BYMONTH=MAR;BYMONTHDAY=10
Next Run On:  2012-03-10 19:47:09 saturday
Next Run On:  2013-03-10 19:47:09 sunday
Next Run On:  2014-03-10 19:47:09 monday
Next Run On:  2015-03-10 19:47:09 tuesday
Next Run On:  2016-03-10 19:47:09 thursday
Next Run On:  2017-03-10 19:47:09 friday
Next Run On:  2018-03-10 19:47:09 saturday
Next Run On:  2019-03-10 19:47:09 sunday
Next Run On:  2020-03-10 19:47:09 tuesday
Next Run On:  2021-03-10 19:47:09 wednesday

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

4。每月的最后一个周五
SYS@standby1/2011-04-28 19:55:45>@repeat_interval.sql
Enter value for repeat_interval: FREQ=MONTHLY;BYDAY=-1FRI;
Next Run On:  2011-04-29 19:55:48 friday
Next Run On:  2011-05-27 19:55:48 friday
Next Run On:  2011-06-24 19:55:48 friday
Next Run On:  2011-07-29 19:55:48 friday
Next Run On:  2011-08-26 19:55:48 friday
Next Run On:  2011-09-30 19:55:48 friday
Next Run On:  2011-10-28 19:55:48 friday
Next Run On:  2011-11-25 19:55:48 friday
Next Run On:  2011-12-30 19:55:48 friday
Next Run On:  2012-01-27 19:55:48 friday

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
5。每隔五十个小时
SYS@standby1/2011-04-28 19:55:48>@repeat_interval.sql
Enter value for repeat_interval: FREQ=HOURLY;INTERVAl=50;
Next Run On:  2011-04-30 21:56:45 saturday
Next Run On:  2011-05-02 23:56:45 monday
Next Run On:  2011-05-05 01:56:45 thursday
Next Run On:  2011-05-07 03:56:45 saturday
Next Run On:  2011-05-09 05:56:45 monday
Next Run On:  2011-05-11 07:56:45 wednesday
Next Run On:  2011-05-13 09:56:45 friday
Next Run On:  2011-05-15 11:56:45 sunday
Next Run On:  2011-05-17 13:56:45 tuesday
Next Run On:  2011-05-19 15:56:45 thursday

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

6。每月的前三天每隔20个小时
SYS@standby1/2011-04-28 20:01:35>@repeat_interval.sql
Enter value for repeat_interval: freq=hourly;interval=20;bymonthday=1,2,3;
Next Run On:  2011-05-01 08:01:43 sunday
Next Run On:  2011-05-02 04:01:43 monday
Next Run On:  2011-05-03 00:01:43 tuesday
Next Run On:  2011-05-03 20:01:43 tuesday
Next Run On:  2011-06-01 04:01:43 wednesday
Next Run On:  2011-06-02 00:01:43 thursday
Next Run On:  2011-06-02 20:01:43 thursday
Next Run On:  2011-06-03 16:01:43 friday
Next Run On:  2011-07-01 04:01:43 friday
Next Run On:  2011-07-02 00:01:43 saturday

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
7。每个月的1 15 最后一天
SYS@standby1/2011-04-28 20:05:44>@repeat_interval.sql
Enter value for repeat_interval: freq=monthly;bymonthday=1,-1,15;
Next Run On:  2011-04-30 20:05:54 saturday
Next Run On:  2011-05-01 20:05:54 sunday
Next Run On:  2011-05-15 20:05:54 sunday
Next Run On:  2011-05-31 20:05:54 tuesday
Next Run On:  2011-06-01 20:05:54 wednesday
Next Run On:  2011-06-15 20:05:54 wednesday
Next Run On:  2011-06-30 20:05:54 thursday
Next Run On:  2011-07-01 20:05:54 friday
Next Run On:  2011-07-15 20:05:54 friday
Next Run On:  2011-07-31 20:05:54 sunday

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
8。每年十二月最后一个星期五
SYS@standby1/2011-04-28 20:08:31>@repeat_interval.sql
Enter value for repeat_interval: freq=yearly;bymonth=12;byday=-1fri;
Next Run On:  2011-12-30 20:08:39 friday
Next Run On:  2012-12-28 20:08:39 friday
Next Run On:  2013-12-27 20:08:39 friday
Next Run On:  2014-12-26 20:08:39 friday
Next Run On:  2015-12-25 20:08:39 friday
Next Run On:  2016-12-30 20:08:39 friday
Next Run On:  2017-12-29 20:08:39 friday
Next Run On:  2018-12-28 20:08:39 friday
Next Run On:  2019-12-27 20:08:39 friday
Next Run On:  2020-12-25 20:08:39 friday

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
9.每年的1 6 12月的后三天 的晚上十点
SYS@standby1/2011-04-28 20:12:20>@repeat_interval.sql
Enter value for repeat_interval: freq=yearly;bymonth=1,6,12;bymonthday=-3,-2,-1;byhour=22;
Next Run On:  2011-06-28 22:12:24 tuesday
Next Run On:  2011-06-29 22:12:24 wednesday
Next Run On:  2011-06-30 22:12:24 thursday
Next Run On:  2011-12-29 22:12:24 thursday
Next Run On:  2011-12-30 22:12:24 friday
Next Run On:  2011-12-31 22:12:24 saturday
Next Run On:  2012-01-29 22:12:24 sunday
Next Run On:  2012-01-30 22:12:24 monday
Next Run On:  2012-01-31 22:12:24 tuesday
Next Run On:  2012-06-28 22:12:24 thursday

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

10。每星期一到五 的7 18 点
SYS@standby1/2011-04-28 20:18:19>@repeat_interval.sql
Enter value for repeat_interval: freq=weekly;byday=mon,tue,wed,thu,fri;byhour=7,18;
Next Run On:  2011-04-28 07:00:00 thursday
Next Run On:  2011-04-28 18:00:00 thursday
Next Run On:  2011-04-29 07:00:00 friday
Next Run On:  2011-04-29 18:00:00 friday
Next Run On:  2011-05-02 07:00:00 monday
Next Run On:  2011-05-02 18:00:00 monday
Next Run On:  2011-05-03 07:00:00 tuesday
Next Run On:  2011-05-03 18:00:00 tuesday
Next Run On:  2011-05-04 07:00:00 wednesday
Next Run On:  2011-05-04 18:00:00 wednesday

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
11。每周周六 日的早上六点
SYS@standby1/2011-04-28 20:19:35>@repeat_interval.sql
Enter value for repeat_interval: freq=weekly;byday=sat,sun;byhour=6;
Next Run On:  2011-04-30 06:00:00 saturday
Next Run On:  2011-05-01 06:00:00 sunday
Next Run On:  2011-05-07 06:00:00 saturday
Next Run On:  2011-05-08 06:00:00 sunday
Next Run On:  2011-05-14 06:00:00 saturday
Next Run On:  2011-05-15 06:00:00 sunday
Next Run On:  2011-05-21 06:00:00 saturday
Next Run On:  2011-05-22 06:00:00 sunday
Next Run On:  2011-05-28 06:00:00 saturday
Next Run On:  2011-05-29 06:00:00 sunday

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24890594/viewspace-694041/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24890594/viewspace-694041/