How to use repeat_interval in Oracle Scheduler
Editor to share with you how to use repeat_interval in Oracle Scheduler, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!
Repeat_interval literally means repetition interval. Refers to a user-defined interval at which to perform a specified task. If you do not specify this parameter, the task is executed only once.
The repeat_interval syntax is as follows:
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
Here are several common subnames. For more information, please refer to the official document: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#BABFBCEF
1 、 frequency_clause
The syntax is as follows:
Frequency_clause = "FREQ"=" (predefined_frequency | user_defined_frequency) predefined_frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" | "HOURLY" | "MINUTELY" | "SECONDLY" user_defined_frequency = named_schedule
Specifies the type of repetition, which must be specified. The meaning of each value can be understood literally: YEARLY specifies annual repetition, MONTHLY specifies monthly repetition, WEEKLY specifies weekly repetition, DAILY specifies daily repetition, HOURLY specifies hourly repetition, MINUTELY specifies minute repetition, and SECONDLY specifies second repetition.
Example: use MINUTELY as a repeat type
BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=minutely' ENABLED = > TRUE) END;/
Create a job named tst1 and make the following query:
As you can see from the above query, the interval between the last execution and the next execution is about 60s.
2 、 interval_clause
The syntax is as follows:
Interval_clause = "INTERVAL" = "intervalnum intervalnum = 1 through 99
This parameter specifies the repetition interval. The default is 1, and the value range is 1-99.
Example: use MINUTELY as the repeat type, specifying an interval of 2 minutes
BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=minutely Interval=2', ENABLED = > TRUE); END;/
Interval between queries again
As can be seen from the above picture, the interval is about 2 minutes.
3 、 bymonth_clause
The syntax is as follows:
Bymonth_clause = "BYMONTH" = "monthlist monthlist = month (", "month) * 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 "
This parameter is used to specify which month or months to perform the task. You can specify a month with a number or a three-letter abbreviation.
Example: specify that job be executed every March, June, September, and December
BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=monthly Bymonth=3,6,9,12', ENABLED = > TRUE); END;/
As you can see from the above query, the next time job executes is June 15, 17, and because it is May, job is not executed.
4 、 byweekno_clause
Byweekno_clause = "BYWEEKNO" = "weeknumber_list weeknumber_list = weeknumber (", "weeknumber) * weeknumber = [minus] weekno weekno = 1 through 53
This parameter is used to specify the week in which job is executed, 52 or 53 weeks per year, and is valid only for FREQ=YEARLY.
5 、 byyearday_clause
Byyearday_clause = "BYYEARDAY" = "yearday_list yearday_list = yearday (", "yearday) * yearday = [minus] yeardaynum yeardaynum = 1 through 366
This parameter specifies the day of the year on which job is executed. Valid values are 1-366. you can also specify "-" before the number to represent the penultimate day of the year, such as "- 2".
Example: specify the penultimate day of each year to execute job
BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=yearly Byyearday=-2', ENABLED = > TRUE); END;/
As can be seen from the chart above, the execution time of job is December 30th, the penultimate day of 17 years.
6 、 bydate_clause
The syntax is as follows:
Bydate_clause = "BYDATE" = "date_list date_list = date (", "date) * date = [YYYY] MMDD [offset | span]
This parameter is used to specify the date on which the job will be executed in the format [YYYY] MMDD. You can use the span parameter to specify consecutive dates such as bydate=0110,0111,0112,0113,0114 is equivalent to bydate=0110+span:5d. The date can be adjusted with the offset parameter, increasing / decreasing a few days (d) or weeks (w), such as bydate=0520-offset:5d, indicating that it will be executed on May 15th.
7 、 byday_clause
The syntax is as follows:
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 "
Specify the day of the week to execute the job, using the first three letters of the word. If you specify a freq=yearly, the daynum can be 1-53, specifying the week ordinal of the year. If freq=monthly, the daynum can be 1-5, specifying the week ordinal of a month. Use "-" to denote the countdown.
Example: execute job on Friday of the third week of each month
BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=monthly Byday=3 FRI', ENABLED = > TRUE); END;/
As can be seen from the picture above, the execution time is May 19, 17, and it is the Friday of the third week of May.
8 、 bymonthday_clause
The syntax is as follows:
Bymonthday_clause = "BYMONTHDAY" = "monthday_list monthday_list = monthday (", "monthday) * monthday = [minus] monthdaynum monthdaynum = 1 through 31
Specify the day of the month to execute job, valid values are 1-31, or you can use the "-" symbol to specify the penultimate day of the month.
Example: specify the penultimate day of each month to execute job
BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=hourly Bymonthday=-2', ENABLED = > TRUE); END;/
As shown in the figure above, the next time to execute job is May 30th, the penultimate day of May.
9 、 byhour_clause 、 byminute_clause 、 bysecond_clause
The syntax is as follows:
Byhour_clause = "BYHOUR" = "hour_list hour_list = hour (", "hour) * hour = 0 through 23byminute_clause =" BYMINUTE "=" minute_list minute_list = minute ("," minute) * minute = 0 through 59bysecond_clause = "BYSECOND" = "second_list second_list = second (", "second) * second = 0 through 59
These parameters specify the specific hours, minutes, and seconds when the job executes.
What time is specified by byhour, minutes by byminute, and seconds by bysecond?
Example: execute job at 1:20:30 every day
BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB (JOB_NAME = > 'tst1', JOB_TYPE = >' plsql_block', JOB_ACTION = > 'insert into emp select * from scott.emp where empno=7396', REPEAT_INTERVAL = >' freq=daily;interval=1;byhour=1;byminute=20 Bysecond=30', ENABLED = > TRUE); END;/
As can be seen from the above figure, the time of one execution is 1:20:30 on May 16.
The above is all the contents of the article "how to use repeat_interval in Oracle Scheduler". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
