oracle scheduler(三)创建…
来源:互联网 发布:知否小说全文免费阅读 编辑:程序博客网 时间:2024/05/20 02:24
三、使用Schedules
3.1 创建和管理Schedules
SQL>desc dbms_scheduler.create_schedule;
Parameter Type Mode Default?
--------------- ------------------------ ---- --------
SCHEDULE_NAME VARCHAR2 IN
START_DATE TIMESTAMP WITH TIME ZONE IN Y
REPEAT_INTERVAL VARCHAR2 IN
END_DATE TIMESTAMP WITH TIME ZONE IN Y
COMMENTS VARCHAR2 IN Y
各参数分别代表含意如下:
(1)SCHEDULE_NAME:指定schedule名称,注意名称不能重复。
(2) START_DATE:指定该调度的开始时间,可为空,当为空时表示该调度暂不起用。
(3) REPEAT_INTERVAL:指定调度的执行频率或周期。
(4) END_DATE:指定调度的结束时间,可为空,为空时就表示该调度将一直进行。
(5) COMMENTS:注释信息。
(1) FREQ 关键字用来指定间隔的时间周期,可选参数有:YEARLY,MONTHLY, WEEKLY, DAILY,
HOURLY, MINUTELY, and SECONDLY,分别表示年、月、周、日、时、分、秒等单位。
(2) INTERVAL关键字用来指定间隔的频繁,可指定的值的范围从1-99。
比如说,当指定REPEAT_INTERVAL=>'FREQ=DAILY;INTERVAL=1';就表示每天执行一次,如果将INTERVAL 改为7 就表示每7 天执行一次,效果等同于FREQ=WEEKLY;INTERVAL=1。
下面,创建一个schedule,指定调度为每周一次的频率,执行脚本如下:
SQL> BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'MySchedule',
start_date => SYSDATE,
repeat_interval => 'FREQ=WEEKLY; INTERVAL=1',
comments => 'Every 1 weeks');
END;
/
SQL> select schedule_name,repeat_interval fromuser_scheduler_schedules;
sql> execdbms_scheduler.drop_schedule('my_first_schedule');
pl/sql procedure successfully completed.
3.2 Schedules调度Programs执行的Jobs
1.我们用前面创建的Program:IPCONFIG,执行操作系统命令ipconfig。(创建的IPCONFIG:参见上篇博文)
2. 用我们刚创建的schedule:MySchedule
3. 创建job,按照指定的schedule,执行program,操作如下:
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'ExecCmd',
program_name => 'IPCONFIG',
schedule_name => 'MySchedule',
enabled => true);
END;
/
SQL> select log_id,log_date,status,additional_infofrom user_scheduler_job_run_details where job_name ='ExecCmd';
3.3 设置Repeat Interval
REPEAT_INTERVAL 参数的详细语法如下:
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 [";" include_clause]
[";" exclude_clause] [";" intersect_clause]
frequency_clause = "FREQ" "=" ( predefined_frequency | user_defined_frequency )
predefined_frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY"|
"HOURLY" | "MINUTELY" | "SECONDLY"
user_defined_frequency = named_schedule
==============================
interval_clause = "INTERVAL" "=" intervalnum
intervalnum = 1 through 99
bymonth_clause = "BYMONTH" "=" monthlist
monthlist = monthday ( "," monthday)*
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"
byweekno_clause = "BYWEEKNO" "=" weeknumber_list
weeknumber_list = weeknumber ( "," weeknumber)*
weeknumber = [minus] weekno
weekno = 1 through 53
byyearday_clause = "BYYEARDAY" "=" yearday_list
yearday_list = yearday ( "," yearday)*
yearday = [minus] yeardaynum
yeardaynum = 1 through 366
bydate_clause = "BYDATE" "=" date_list
date_list = date ( "," date)*
date = [YYYY]MMDD [ offset | span ]
bymonthday_clause = "BYMONTHDAY" "=" monthday_list
monthday_list = monthday ( "," monthday)*
monthday = [minus] monthdaynum
monthdaynum = 1 through 31
byday_clause = "BYDAY" "=" byday_list
byday_list = byday ( "," byday)*
byday = [weekdaynum] day
weekdaynum = [minus] daynum
daynum = 1 through 53
daynum = 1 through 5
day = "MON" | "TUE" | "WED" | "THU" | "FRI" | "SAT" | "SUN"
byhour_clause = "BYHOUR" "=" hour_list
hour_list = hour ( "," hour)*
hour = 0 through 23
byminute_clause = "BYMINUTE" "=" minute_list
minute_list = minute ( "," minute)*
minute = 0 through 59
bysecond_clause = "BYSECOND" "=" second_list
second_list = second ( "," second)*
second = 0 through 59
bysetpos_clause = "BYSETPOS" "=" setpos_list
setpos_list = setpos ("," setpos)*
setpos = [minus] setpos_num
setpos_num = 1 through 9999
==============================
include_clause = "INCLUDE" "=" schedule_list
exclude_clause = "EXCLUDE" "=" schedule_list
intersect_clause = "INTERSECT" "=" schedule_list
schedule_list = schedule_clause ("," schedule_clause)*
schedule_clause = named_schedule [ offset ]
named_schedule = [schema "."] schedule
periods_clause = "PERIODS" "=" periodnum
byperiod_clause = "BYPERIOD" "=" period_list
period_list = periodnum ("," periodnum)*
periodnum = 1 through 100
==============================
offset = (" " | "-") ["OFFSET:"] duration_val
span = (" " | "-" | "^") "SPAN:" duration_val
duration_val = dur-weeks | dur_days
dur_weeks = numofweeks "W"
dur_days = numofdays "D"
numofweeks = 1 through 53
numofdays = 1 through 376
minus = "-"
例如:设置任务仅在周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';
REPEAT_INTERVAL => 'trunc(sysdate) 1'
又比如设置任务每周执行一次:
REPEAT_INTERVAL => 'trunc(sysdate) 7'
转自:http://blog.csdn.net/tianlesoftware/article/details/4715218
0 0
- oracle scheduler(三)创建…
- oracle scheduler(二)创建…
- oracle scheduler(一)创建…
- oracle scheduler(六)使用J…
- oracle scheduler(七)使用W…
- oracle scheduler(四)使用Events
- oracle scheduler(五)使用Chains
- oracle 创建scheduler,job
- ORACLE Scheduler特性(1)创建jobs
- 学习ORACLE Scheduler特性(9)创建Chains
- oracle job创建
- oracle 入门1 创建用户、权限、角…
- Oracle Scheduler(三)——Jobs(Repeat Interval参数)!
- 全面学习ORACLE Scheduler特性(4)创建和管理Schedule
- 全面学习ORACLE Scheduler特性(1)创建jobs
- 全面学习ORACLE Scheduler特性(1)创建jobs
- 全面学习ORACLE Scheduler特性(4)创建和管理Schedule
- 全面学习ORACLE Scheduler特性(1)创建jobs
- Oracle专用服务器与共享服务器的区…
- oracle设置共享服务器模式
- 自动pga管理
- V$SESSION_LONGOPS
- oracle scheduler(二)创建…
- oracle scheduler(三)创建…
- PHP取得当前插入数据库中记录的id值
- oracle scheduler(四)使用Events
- oracle scheduler(五)使用Chains
- oracle scheduler(六)使用J…
- oracle scheduler(一)创建…
- oracle scheduler(七)使用W…
- Oracle的时区问题
- oracle time_zone