11g调度--scheduler使用

CREATE_SCHEDULE Procedure

This procedure creates a schedule.

DBMS_SCHEDULER.CREATE_SCHEDULE (
   schedule_name          IN VARCHAR2,
   start_date             IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
   repeat_interval        IN VARCHAR2,
   end_date               IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
   comments               IN VARCHAR2 DEFAULT NULL);

schedule_name	The name to assign to the schedule. The name must be unique in the SQL namespace. For example, a schedule cannot have the same name as a table in a schema. If no name is specified, then an error occurs. 名称唯一，必须指定！！
start_date	This attribute specifies the first date and time on which this schedule becomes valid. For a repeating schedule, the value for start_date is a reference date. In this case, the start of the schedule is not the start_date; it depends on the repeat interval specified. start_date is used to determine the first instance of the schedule. If start_date is specified in the past and no value for repeat_interval is specified, the schedule is invalid. For a repeating job or window, start_date can be derived from the repeat_interval if it is not specified. If start_date is null, then the date that the job or window is enabled is used. start_date and repeat_interval cannot both be null. 该参数指定了什么时候开始schedule。对于重复的时间 取决于第一次实例化的schedule
repeat_interval	This attribute specifies how often the schedule repeats. It is expressed using calendaring syntax. See "Calendaring Syntax" for further information. PL/SQL expressions are not allowed as repeat intervals for named schedules. 该参数决定schedule执行的周期  具体用法在下文中列出
end_date	The date and time after which jobs will not run and windows will not open. A non-repeating schedule that has no end_date is valid forever. end_date has to be after the start_date. If this is not the case, then an error is generated when the schedule is created.
comments	This attribute specifies an optional comment about the schedule. By default, this attribute is NULL.

注意：权限问题

This procedure requires the CREATE JOB privilege to create a schedule in your own schema or the CREATE ANY JOB privilege to create a schedule in someone else's schema by specifying schema.schedule_name. Once a schedule has been created, it can be used by other users. The schedule is created with access to PUBLIC. Therefore, there is no need to explicitly grant access to the schedule.

repeat_interval：用法：

例如：设置任务仅在周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';