DBMS Scheduler设置重复间隔

文档搬运工

于 2023-08-31 16:55:12 发布

阅读量328

点赞数

分类专栏： Oracle Scheduler 文章标签： oracle

本文链接：https://blog.csdn.net/xxzhaobb/article/details/132606775

版权

Oracle Scheduler 专栏收录该内容

5 篇文章

订阅专栏

本文详细解释了如何在Oracle数据库中使用Scheduler的calendaringexpressions设置作业重复间隔，包括基本频率选项如每日、每周、每月等，以及更复杂的PL/SQL表达式示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考文档：

Database Administrator’s Guide

29.4.5.2 Using the Scheduler Calendaring Syntax

The main way to set how often a job repeats is to set the repeat_interval attribute with a Scheduler calendaring expression.

See Also:

Oracle Database PL/SQL Packages and Types Reference for a detailed description of the calendaring syntax for repeat_interval as well as the CREATE_SCHEDULE procedure

Examples of Calendaring Expressions

The following examples illustrate simple repeat intervals. For simplicity, it is assumed that there is no contribution to the evaluation results by the start date.

Run every Friday. (All three examples are equivalent.)

FREQ=DAILY; BYDAY=FRI;
FREQ=WEEKLY; BYDAY=FRI;
FREQ=YEARLY; BYDAY=FRI;

Run every other Friday.

FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;

Run on the last day of every month.

FREQ=MONTHLY; BYMONTHDAY=-1;

Run on the next to last day of every month.

FREQ=MONTHLY; BYMONTHDAY=-2;

Run on March 10th. (Both examples are equivalent)

FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;
FREQ=YEARLY; BYDATE=0310;

Run every 10 days.

FREQ=DAILY; INTERVAL=10;

Run daily at 4, 5, and 6PM.

FREQ=DAILY; BYHOUR=16,17,18;

Run on the 15th day of every other month.

FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;

Run on the 29th day of every month.

FREQ=MONTHLY; BYMONTHDAY=29;

Run on the second Wednesday of each month.

FREQ=MONTHLY; BYDAY=2WED;

Run on the last Friday of the year.

FREQ=YEARLY; BYDAY=-1FRI;

Run every 50 hours.

FREQ=HOURLY; INTERVAL=50;

Run on the last day of every other month.

FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=-1;

Run hourly for the first three days of every month.

FREQ=HOURLY; BYMONTHDAY=1,2,3;

Here are some more complex repeat intervals:

Run on the last workday of every month (assuming that workdays are Monday through Friday).

FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1

Run on the last workday of every month, excluding company holidays. (This example references an existing named schedule called Company_Holidays.)

FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; EXCLUDE=Company_Holidays; BYSETPOS=-1

Run at noon every Friday and on company holidays.

FREQ=YEARLY;BYDAY=FRI;BYHOUR=12;INCLUDE=Company_Holidays

Run on these three holidays: July 4th, Memorial Day, and Labor Day. (This example references three existing named schedules, JUL4, MEM, and LAB, where each defines a single date corresponding to a holiday.)

JUL4,MEM,LAB

Examples of Calendaring Expression Evaluation

A repeat interval of "FREQ=MINUTELY;INTERVAL=2;BYHOUR=17; BYMINUTE=2,4,5,50,51,7;" with a start date of 28-FEB-2004 23:00:00 will generate the following schedule:

SUN 29-FEB-2004 17:02:00
SUN 29-FEB-2004 17:04:00
SUN 29-FEB-2004 17:50:00
MON 01-MAR-2004 17:02:00
MON 01-MAR-2004 17:04:00
MON 01-MAR-2004 17:50:00
...

A repeat interval of "FREQ=MONTHLY;BYMONTHDAY=15,-1" with a start date of 29-DEC-2003 9:00:00 will generate the following schedule:

WED 31-DEC-2003 09:00:00
THU 15-JAN-2004 09:00:00
SAT 31-JAN-2004 09:00:00
SUN 15-FEB-2004 09:00:00
SUN 29-FEB-2004 09:00:00
MON 15-MAR-2004 09:00:00
WED 31-MAR-2004 09:00:00
...

A repeat interval of "FREQ=MONTHLY;" with a start date of 29-DEC-2003 9:00:00 will generate the following schedule. (Note that because there is no BYMONTHDAY clause, the day of month is retrieved from the start date.)

MON 29-DEC-2003 09:00:00
THU 29-JAN-2004 09:00:00
SUN 29-FEB-2004 09:00:00
MON 29-MAR-2004 09:00:00
...

Example of Using a Calendaring Expression

As an example of using the calendaring syntax, consider the following statement:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'scott.my_job1',
   start_date           => '15-JUL-04 01.00.00 AM Europe/Warsaw',
   repeat_interval      => 'FREQ=MINUTELY; INTERVAL=30;',
   end_date             => '15-SEP-04 01.00.00 AM Europe/Warsaw',
   comments             => 'My comments here');
END;
/

This creates my_job1 in scott. It will run for the first time on July 15th and then run until September 15. The job is run every 30 minutes.

Parent topic: Setting the Repeat Interval

29.4.5.3 Using a PL/SQL Expression

When you need more complicated capabilities than the calendaring syntax provides, you can use PL/SQL expressions. You cannot, however, use PL/SQL expressions for windows or in named schedules. The PL/SQL expression must evaluate to a date or a timestamp.

Other than this restriction, there are no limitations, so with sufficient programming, you can create every possible repeat interval. As an example, consider the following statement:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'scott.my_job2', 
   start_date           => '15-JUL-04 01.00.00 AM Europe/Warsaw',
   repeat_interval      => 'SYSTIMESTAMP + INTERVAL '30' MINUTE',
   end_date             => '15-SEP-04 01.00.00 AM Europe/Warsaw',
   comments             => 'My comments here');
END;
/

This creates my_job1 in scott. It will run for the first time on July 15th and then every 30 minutes until September 15. The job is run every 30 minutes because repeat_interval is set to SYSTIMESTAMP + INTERVAL '30' MINUTE, which returns a date 30 minutes into the future.

Parent topic: Setting the Repeat Interval

END