Use of Scheduler in Oracle

Scheduler Use Detailed:

Create Scheduler:

BEGIN

Sys.dbms_scheduler.create_schedule (

Repeat_interval => ' freq=daily; byhour=15; byminute=0; Bysecond=0 ',

Start_date => Systimestamp at time zone ' +8:00 ',

End_date => To_timestamp_tz (' 2014-03-15 +8:00 ', ' yyyy-mm-dd tzh:tzm '),

Comments => ' REBUILD INDEX ',

Schedule_name => ' "SYS". " Idx_rebuild "');

End;

Parameter explanation:

Repeat_interval Set Repeat Frequency

Freq (Specify repeat frequency):

Yearly: In years, you can set the day of the year to perform. Interval the number of repetitions. Byyearday can specify a specific day. 1-365 is a day, but a leap year is 1-366. Due to the existence of this uncertainty in the February (for example, if setting 69, excepting refers to March 10, leap year will be March 9), so we can use 1-59 to represent January to February 28,-307 to-one means February 2 September-December 31. Byhour,byminute,bysecond can then be used to specify the specific moment of operation.

Others are monthly, WEEKLY, daily, hourly, minutely, secondly

An example is provided:

January 14 and March 10 of each year, 5 seconds of implementation, three years:

Repeat_interval => ' freq=yearly;interval=3; byyearday=14,-297; byhour=15; byminute=10; Bysecond=5 ',

Monthly: In months, set the day of the month to execute:

Executes three months, 1st, 3rd, 5 seconds per month:

Repeat_interval => ' freq=monthly;interval=3; bymonthday=1,3; byhour=15; byminute=10; Bysecond=5 ',

Every Tuesday Wednesday 3:10 P.M. 5 seconds, execution three weeks:

Repeat_interval => ' freq=weekly;interval=3; byday=tue,wed; byhour=15; byminute=10; Bysecond=5 ',

Executes three days, 5 seconds per day, 3:10 P.M.

Repeat_interval => ' freq=daily;interval=3; byhour=15; byminute=10; Bysecond=5 ',

Executes three times per hour from the first execution: (minutely, secondly and similar, no writing)

Repeat_interval => ' freq=hourly;interval=3 ',

The call was executed at that time without repeating:

Repeat_interval => NULL,

Start_date Setting Effective Time:

Take effect immediately

Start_date => Systimestamp at time zone ' +8:00 ',

2014-03-15 five o'clock in the afternoon effective:

start_date => To_timestamp_tz (' 2014-03-15 17:00:00 +8:00 ', ' yyyy-mm-dd HH24:MI:SS '),

End_date Set Schedule Expiration time:

If there is no expiration time, you do not have to set this row.

2014-03-15 Nine o'clock in the afternoon end:

End_date => To_timestamp_tz (' 2014-03-15 21:10:00 +8:00 ', ' yyyy-mm-dd HH24:MI:SS '),

Comments some descriptions of scheduler, no can not write.

Schedule_name:schedule's user and name

SYS user, the name is Idx_rebuild

Schedule_name => ' "SYS". " Idx_rebuild "');

Modify Scheduler:

BEGIN

Sys.dbms_scheduler.set_attribute (name => ' SYS ".) Dailyrebuild "', attribute => ' Repeat_interval ', value => ' freq=weekly; byhour=14; byminute=0; Bysecond=0 ');

Sys.dbms_scheduler.set_attribute (name => ' SYS ".) Dailyrebuild "', attribute => ' start_date ', value => systimestamp at time zone ' +8:00 ');

Sys.dbms_scheduler.set_attribute (name => ' SYS ".) Dailyrebuild "', attribute => ' end_date ', Value => to_timestamp_tz (' 2014-03-15 +8:00 ', ' yyyy-mm-dd tzh:tzm ')";

End;

Name: The user and name of the modified scheduler

Attribute: Modified parameter name

Value: the modified parameter value

Delete Scheduler:

BEGIN

Dbms_scheduler.drop_schedule (schedule_name => ' dailyrebuild ', Force => FALSE);

End;

Only two parameters

Schedule_name:schedule's name.

Force: If it is false, if there are other jobs that are using this schedule will not delete this schedule, if it is true if no other job use this schedule will delete this schedule.

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/