Repeat_interval in Oracle Scheduler

Scheduler (Dispatch) was introduced in the Oracle 11g release to replace the previous version of the Job (Task). Here is a brief introduction to the meaning and use of the Repeat_interval parameter in scheduler.

Repeat_interval literally means repetition interval. Refers to how long a user-defined interval performs the specified task. If you do not specify this parameter, the task executes 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 a few common sub-names, more detailed information can 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 repeating type, which must be specified. The meanings of each value are literally understandable: yearly specifies repeating by year, monthly specifies repeating by month, weekly specifies repeating by week, daily specifies repeating by day, hourly specifies to repeat by hour, minutely specifies to repeat by minute, Secondly specifies to repeat in seconds.

Example: Using minutely as a repeating 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:

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/95/CD/wKioL1kZwkyiDAG-AABH0Hcv-vs358.png "title=" 1.png "alt=" Wkiol1kzwkyidag-aabh0hcv-vs358.png "/>

As you can see from the above query, the interval between the last execution and the next execution is approximately 60s.

2, Interval_clause

The syntax is as follows:

Interval_clause = "Interval" "=" intervalnum Intervalnum = 1 through 99

This parameter specifies the repeat interval, which defaults to 1 and the value range is 1-99

Example: Using minutely as a repeating type, specifying a 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;/

Query interval time again

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/95/CD/wKiom1kZw7yS4GvMAABEDdHzWvY145.png "title=" 1.png "alt=" Wkiom1kzw7ys4gvmaabeddhzwvy145.png "/> can be seen from the time interval of 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 ... Char_month = "JAN" | "FEB" | "MAR" | "APR" | "May" |   "June" | "JUL" | "The" | "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 you can specify a month with a three-letter abbreviation.

Example: Specifying a job to execute every 3, 6, 9, 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 ', &Nbsp;                                ENABLED          => true); end;/

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/95/CE/wKiom1kZxPLAV3J4AAA95FxLaHU807.png "title=" 1.png "alt=" Wkiom1kzxplav3j4aaa95fxlahu807.png "/>

As can be seen from the above query, the next job execution time is June 15, 17, because it is now May, so the 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 specifies that the job is performed during the week of the year, 52 or 53 weeks per year, and that the parameter 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 in which the job is executed, the valid value is 1-366, or the "-" in front of the number that represents the penultimate day of the year, such as "2" for December 30.

Example: Specify the next day of the year to perform the 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;/

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/95/CE/wKioL1kZx9KA_TqJAAA8PoYFXNo579.png "title=" 1.png "alt=" Wkiol1kzx9ka_tqjaaa8poyfxno579.png "/>

As can be seen, the job execution time is December 30, 17, that is the penultimate day of 17.

6, Bydate_clause

The syntax is as follows:

Bydate_clause = "Bydate" "=" date_list date_list = Date ("," date) * date = [YYYY]MMDD [offset | span]

This parameter specifies the day on which to execute the job, in the format [YYYY]MMDD. You can use the span parameter to specify consecutive dates such as bydate=0110,0111,0112,0113,0114 and bydate=0110+span:5d equivalents. You can adjust the date with the offset parameter to increase/decrease the number of days (d) or weeks (W) such as bydate=0520-offset:5d, indicating that the specified May 15 execution.

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" 

Specifies the week of the week for the execution job, using the first three letters of the word. If the specified freq=yearly, then daynum can be 1-53, specifying the week ordinal of a year. If freq=monthly, then daynum can be 1-5, specifying the week ordinal of one months. Use "-" to indicate reciprocal.

Example: Friday execution job for the 3rd week of the 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 ', &Nbsp;                                ENABLED          => true); end;/

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/95/CF/wKiom1kZ0GfQZ7UXAAA_zIBbGQM537.png "title=" 1.png "alt=" Wkiom1kz0gfqz7uxaaa_zibbgqm537.png "/>

It can be seen that the execution time is May 19, 17 for the third week of May in Friday.

8, Bymonthday_clause

The syntax is as follows:

Bymonthday_clause = "ByMonthDay" "=" monthday_list monthday_list = MonthDay ("," monthday) * MonthDay = [minus] Monthd Aynum Monthdaynum = 1 through 31

Specify the day of the month to execute the job, the valid value is 1-31, or you can use the "-" symbol to specify the last day of the month.

Example: Specify the next day of the month to execute the 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;/

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/95/CE/wKioL1kZzCyCdvKHAAA8BIT5-VU699.png "title=" 1.png "alt=" Wkiol1kzzcycdvkhaaa8bit5-vu699.png "/>

As shown, the next time the job is executed is May 30, the 2nd day in 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.

Byhour specify a few points, byminute specify a few, Bysecond specify a few seconds

Example: Perform a job every 1:20:30

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;/

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/95/CF/wKioL1kZzaHCkwVdAAA8MUQ_Lj0840.png "title=" 1.png "alt=" Wkiol1kzzahckwvdaaa8muq_lj0840.png "/>

As you can see, the time of execution is the specified May 16 1:20:30.

The above only describes some of the parameters, but also only some parameters to the example, the use of specific parameters can be in the actual production process to try.

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1926058

Repeat_interval in Oracle Scheduler