ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 设置Repeat Interval参数(实例参考)

设置Repeat Interval参数(实例参考)

原创 Linux操作系统 作者:xpj0515 时间:2011-04-28 20:55:44 0 删除 编辑

    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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-08

  • 博文量
    32
  • 访问量
    55236