当前位置: 首页 > news >正文 全面学习ORACLE Scheduler特性(6)设置Repeat Interval参数 news 来源:原创 2024/5/4 17:19:51 3.3 设置Repeat Interval Job 和Schedule中REPEAT_INTERVAL参数都是用来控制执行的频率或周期,虽然说周期是一个时间性概念,不过REPEAT_INTERVAL指定的时候并不是一个时间值,而是由一组关键字描述的时间。 除了前面介绍Job和Schedule的REPEAT_INTERVAL参数时,提到该参数拥有FREQ以及INTERVAL两个关键字,其实除此之外,还有如BYMONTH、BYWEEKNO、BYYEARDAY、BYDATE等等参数,可以用来进行更精确的定义,比如通过BYMONTH关键字指定调度运行的月份,BYDAY指定调度在哪天运行等等。 REPEAT_INTERVAL 参数的详细语法如下: repeat_interval = regular_schedule | combined_schedule ============================== regular_schedule = frequency_clause [";" interval_clause] [";" bymonth_clause] [";" byweekno_clause] [";" byyearday_clause] [";" bydate_clause] [";" bymonthday_clause] [";" byday_clause] [";" byhour_clause] [";" byminute_clause] [";" bysecond_clause] [";" bysetpos_clause] [";" include_clause] [";" exclude_clause] [";" intersect_clause][";" periods_clause] [";" byperiod_clause] ============================== combined_schedule = schedule_list [";" include_clause] [";" exclude_clause] [";" intersect_clause] frequency_clause = "FREQ" "=" ( predefined_frequency | user_defined_frequency ) predefined_frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" | "HOURLY" | "MINUTELY" | "SECONDLY" user_defined_frequency = named_schedule ============================== interval_clause = "INTERVAL" "=" intervalnum intervalnum = 1 through 99 bymonth_clause = "BYMONTH" "=" monthlist monthlist = monthday ( "," monthday)* month = numeric_month | char_month numeric_month = 1 | 2 | 3 ... 12 char_month = "JAN" | "FEB" | "MAR" | "APR" | "MAY" | "JUN" | "JUL" | "AUG" | "SEP" | "OCT" | "NOV" | "DEC" byweekno_clause = "BYWEEKNO" "=" weeknumber_list weeknumber_list = weeknumber ( "," weeknumber)* weeknumber = [minus] weekno weekno = 1 through 53 byyearday_clause = "BYYEARDAY" "=" yearday_list yearday_list = yearday ( "," yearday)* yearday = [minus] yeardaynum yeardaynum = 1 through 366 bydate_clause = "BYDATE" "=" date_list date_list = date ( "," date)* date = [YYYY]MMDD [ offset | span ] bymonthday_clause = "BYMONTHDAY" "=" monthday_list monthday_list = monthday ( "," monthday)* monthday = [minus] monthdaynum monthdaynum = 1 through 31 byday_clause = "BYDAY" "=" byday_list byday_list = byday ( "," byday)* byday = [weekdaynum] day weekdaynum = [minus] daynum daynum = 1 through 53 /* if frequency is yearly */ daynum = 1 through 5 /* if frequency is monthly */ day = "MON" | "TUE" | "WED" | "THU" | "FRI" | "SAT" | "SUN" byhour_clause = "BYHOUR" "=" hour_list hour_list = hour ( "," hour)* hour = 0 through 23 byminute_clause = "BYMINUTE" "=" minute_list minute_list = minute ( "," minute)* minute = 0 through 59 bysecond_clause = "BYSECOND" "=" second_list second_list = second ( "," second)* second = 0 through 59 bysetpos_clause = "BYSETPOS" "=" setpos_list setpos_list = setpos ("," setpos)* setpos = [minus] setpos_num setpos_num = 1 through 9999 ============================== include_clause = "INCLUDE" "=" schedule_list exclude_clause = "EXCLUDE" "=" schedule_list intersect_clause = "INTERSECT" "=" schedule_list schedule_list = schedule_clause ("," schedule_clause)* schedule_clause = named_schedule [ offset ] named_schedule = [schema "."] schedule periods_clause = "PERIODS" "=" periodnum byperiod_clause = "BYPERIOD" "=" period_list period_list = periodnum ("," periodnum)* periodnum = 1 through 100 ============================== offset = ("+" | "-") ["OFFSET:"] duration_val span = ("+" | "-" | "^") "SPAN:" duration_val duration_val = dur-weeks | dur_days dur_weeks = numofweeks "W" dur_days = numofdays "D" numofweeks = 1 through 53 numofdays = 1 through 376 minus = "-" 这个语法形式看起来复杂无比,其实实用起来很简单,之所以看起来复杂,是因为其功能太过灵活(之前的三思系列笔记中,已经阐述过灵活与复杂的关系),这里不准备逐条解释每一个语法细节,下面将着重通过一些常用设置,希望能够更有助于广大同仁的理解。 例如:设置任务仅在周5的时候运行: REPEAT_INTERVAL => 'FREQ=DAILY; BYDAY=FRI'; REPEAT_INTERVAL => 'FREQ=WEEKLY; BYDAY=FRI'; REPEAT_INTERVAL => 'FREQ=YEARLY; BYDAY=FRI'; 上述三条语句虽然指定的关键字小有差异,不过功能相同。 设置任务隔一周运行一次,并且仅在周5运行: REPEAT_INTERVAL => 'FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI'; 设置任务在当月最后一天运行: REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=-1'; 设置任务在3月10日运行: REPEAT_INTERVAL => 'FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10'; REPEAT_INTERVAL => 'FREQ=YEARLY; BYDATE=0310'; 上述两条语句功能相同。 设置任务每10隔天运行: REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=10'; 设置任务在每天的下午4、5、6点时运行: REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=16,17,18'; 设置任务在每月29日运行: REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=29'; 设置任务在每年的最后一个周5运行: REPEAT_INTERVAL => 'FREQ=YEARLY; BYDAY=-1FRI'; 设置任务每隔50个小时运行: REPEAT_INTERVAL => 'FREQ=HOURLY; INTERVAL=50'; 另外,你是否在怀念常规job中设置interval的简便,虽然功能较弱,但是设置操作非常简单,无须懊恼,其实SCHEDULER中的REPEAT_INTERVAL也完全可以按照那种方式设置,前面都说了,REPEAT_INTERVAL实际上是指定周期,直接指定一个时间值,当然也是周期喽。 比如说,设置任务每天执行一次,也可以设置REPEAT_INTERVAL参数值如下: REPEAT_INTERVAL => 'trunc(sysdate)+1' 又比如设置任务每周执行一次: REPEAT_INTERVAL => 'trunc(sysdate)+7' 不过需要注意,这种方式仅用于创建SCHEDULER中jobs时使用,不能用于schedule。 相关文章: Oracle创建删除用户、角色、表空间、导入导出数据库命令行方式总结 ORACLE计划(schedule)详解 Oracle初学者必知的100个疑问 WML学习(一):概述和基本规则 WML学习(二):基本格式和文件头 WML学习(四):锚和任务 WML学习(三):显示文本 WML学习(五):显示表单 WML学习(七):CGI编程 WML学习(六):事件 UDP数据传输实例 在线播放器代码大全 TcpListener和TcpClient简单例子 C#自定义事件 socket传文件 【译】React性能工程(下) -- 深入研究React性能调试 angular组件开发 centos安装java运行环境jdk+tomcat github从入门到放弃(1) JS数组方法汇总 SpingCloudBus整合RabbitMQ spring boot 整合mybatis 无法输出sql的问题 VirtualBox 安装过程中出现 Running VMs found 错误的解决过程 vue数据传递--我有特殊的实现技巧 Work@Alibaba 阿里巴巴的企业应用构建之路 Zepto.js源码学习之二 阿里研究院入选中国企业智库系统影响力榜 对话 CTO〡听神策数据 CTO 曹犟描绘数据分析行业的无限可能 快速构建spring-cloud+sleuth+rabbit+ zipkin+es+kibana+grafana日志跟踪平台 算法系列——算法入门之递归分而治之思想的实现 新版博客前端前瞻 源码安装memcached和php memcache扩展 DB-Engines 12月数据库排名: PostgreSQL有望获得「2020年度数据库」荣誉? 力扣解法汇总1802. 有界数组中指定下标处的最大值 # 数论-逆元 (BFS)hdoj2377-Bus Pass (Redis使用系列) Springboot 整合Redisson 实现分布式锁 七 (附源码)springboot家庭财务分析系统 毕业设计641323 (九)信息融合方式简介 (九十四)函数和二维数组 (欧拉)openEuler系统添加网卡文件配置流程、(欧拉)openEuler系统手动配置ipv6地址流程、(欧拉)openEuler系统网络管理说明 (七)c52学习之旅-中断 (转)AS3正则:元子符,元序列,标志,数量表达符 .NET : 在VS2008中计算代码度量值 .Net Core和.Net Standard直观理解 .NET 使用配置文件 .net(C#)中String.Format如何使用 .Net中ListT 泛型转成DataTable、DataSet .NET中使用Protobuffer 实现序列化和反序列化 :中兴通讯为何成功 @Bean注解详解 @NoArgsConstructor和@AllArgsConstructor,@Builder @TableLogic注解说明,以及对增删改查的影响 [16/N]论得趣 [Android Studio] 开发Java 程序
3.3 设置Repeat Interval Job 和Schedule中REPEAT_INTERVAL参数都是用来控制执行的频率或周期,虽然说周期是一个时间性概念,不过REPEAT_INTERVAL指定的时候并不是一个时间值,而是由一组关键字描述的时间。 除了前面介绍Job和Schedule的REPEAT_INTERVAL参数时,提到该参数拥有FREQ以及INTERVAL两个关键字,其实除此之外,还有如BYMONTH、BYWEEKNO、BYYEARDAY、BYDATE等等参数,可以用来进行更精确的定义,比如通过BYMONTH关键字指定调度运行的月份,BYDAY指定调度在哪天运行等等。 REPEAT_INTERVAL 参数的详细语法如下: repeat_interval = regular_schedule | combined_schedule ============================== regular_schedule = frequency_clause [";" interval_clause] [";" bymonth_clause] [";" byweekno_clause] [";" byyearday_clause] [";" bydate_clause] [";" bymonthday_clause] [";" byday_clause] [";" byhour_clause] [";" byminute_clause] [";" bysecond_clause] [";" bysetpos_clause] [";" include_clause] [";" exclude_clause] [";" intersect_clause][";" periods_clause] [";" byperiod_clause] ============================== combined_schedule = schedule_list [";" include_clause] [";" exclude_clause] [";" intersect_clause] frequency_clause = "FREQ" "=" ( predefined_frequency | user_defined_frequency ) predefined_frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" | "HOURLY" | "MINUTELY" | "SECONDLY" user_defined_frequency = named_schedule ============================== interval_clause = "INTERVAL" "=" intervalnum intervalnum = 1 through 99 bymonth_clause = "BYMONTH" "=" monthlist monthlist = monthday ( "," monthday)* month = numeric_month | char_month numeric_month = 1 | 2 | 3 ... 12 char_month = "JAN" | "FEB" | "MAR" | "APR" | "MAY" | "JUN" | "JUL" | "AUG" | "SEP" | "OCT" | "NOV" | "DEC" byweekno_clause = "BYWEEKNO" "=" weeknumber_list weeknumber_list = weeknumber ( "," weeknumber)* weeknumber = [minus] weekno weekno = 1 through 53 byyearday_clause = "BYYEARDAY" "=" yearday_list yearday_list = yearday ( "," yearday)* yearday = [minus] yeardaynum yeardaynum = 1 through 366 bydate_clause = "BYDATE" "=" date_list date_list = date ( "," date)* date = [YYYY]MMDD [ offset | span ] bymonthday_clause = "BYMONTHDAY" "=" monthday_list monthday_list = monthday ( "," monthday)* monthday = [minus] monthdaynum monthdaynum = 1 through 31 byday_clause = "BYDAY" "=" byday_list byday_list = byday ( "," byday)* byday = [weekdaynum] day weekdaynum = [minus] daynum daynum = 1 through 53 /* if frequency is yearly */ daynum = 1 through 5 /* if frequency is monthly */ day = "MON" | "TUE" | "WED" | "THU" | "FRI" | "SAT" | "SUN" byhour_clause = "BYHOUR" "=" hour_list hour_list = hour ( "," hour)* hour = 0 through 23 byminute_clause = "BYMINUTE" "=" minute_list minute_list = minute ( "," minute)* minute = 0 through 59 bysecond_clause = "BYSECOND" "=" second_list second_list = second ( "," second)* second = 0 through 59 bysetpos_clause = "BYSETPOS" "=" setpos_list setpos_list = setpos ("," setpos)* setpos = [minus] setpos_num setpos_num = 1 through 9999 ============================== include_clause = "INCLUDE" "=" schedule_list exclude_clause = "EXCLUDE" "=" schedule_list intersect_clause = "INTERSECT" "=" schedule_list schedule_list = schedule_clause ("," schedule_clause)* schedule_clause = named_schedule [ offset ] named_schedule = [schema "."] schedule periods_clause = "PERIODS" "=" periodnum byperiod_clause = "BYPERIOD" "=" period_list period_list = periodnum ("," periodnum)* periodnum = 1 through 100 ============================== offset = ("+" | "-") ["OFFSET:"] duration_val span = ("+" | "-" | "^") "SPAN:" duration_val duration_val = dur-weeks | dur_days dur_weeks = numofweeks "W" dur_days = numofdays "D" numofweeks = 1 through 53 numofdays = 1 through 376 minus = "-" 这个语法形式看起来复杂无比,其实实用起来很简单,之所以看起来复杂,是因为其功能太过灵活(之前的三思系列笔记中,已经阐述过灵活与复杂的关系),这里不准备逐条解释每一个语法细节,下面将着重通过一些常用设置,希望能够更有助于广大同仁的理解。 例如:设置任务仅在周5的时候运行: REPEAT_INTERVAL => 'FREQ=DAILY; BYDAY=FRI'; REPEAT_INTERVAL => 'FREQ=WEEKLY; BYDAY=FRI'; REPEAT_INTERVAL => 'FREQ=YEARLY; BYDAY=FRI'; 上述三条语句虽然指定的关键字小有差异,不过功能相同。 设置任务隔一周运行一次,并且仅在周5运行: REPEAT_INTERVAL => 'FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI'; 设置任务在当月最后一天运行: REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=-1'; 设置任务在3月10日运行: REPEAT_INTERVAL => 'FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10'; REPEAT_INTERVAL => 'FREQ=YEARLY; BYDATE=0310'; 上述两条语句功能相同。 设置任务每10隔天运行: REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=10'; 设置任务在每天的下午4、5、6点时运行: REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=16,17,18'; 设置任务在每月29日运行: REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=29'; 设置任务在每年的最后一个周5运行: REPEAT_INTERVAL => 'FREQ=YEARLY; BYDAY=-1FRI'; 设置任务每隔50个小时运行: REPEAT_INTERVAL => 'FREQ=HOURLY; INTERVAL=50'; 另外,你是否在怀念常规job中设置interval的简便,虽然功能较弱,但是设置操作非常简单,无须懊恼,其实SCHEDULER中的REPEAT_INTERVAL也完全可以按照那种方式设置,前面都说了,REPEAT_INTERVAL实际上是指定周期,直接指定一个时间值,当然也是周期喽。 比如说,设置任务每天执行一次,也可以设置REPEAT_INTERVAL参数值如下: REPEAT_INTERVAL => 'trunc(sysdate)+1' 又比如设置任务每周执行一次: REPEAT_INTERVAL => 'trunc(sysdate)+7' 不过需要注意,这种方式仅用于创建SCHEDULER中jobs时使用,不能用于schedule。