oracle连接时间管理,oracle dbms_scheduler

Oracle

10g新增了scheduler的功能，功能比job强大的很多，Oracle官方也建议将job转移到scheduler 。

先解释一下基础概念，scheduler 包含了以下几个实体对象

Program -- 提供了scheduler 将要运行什么，包含program name，program

type(PLSQL块、存储过程、外部程序)，program

action(具体的程序实体，比如说一个PLSQL块、或者是存储过程的名称、或者是外部程序的路径加名称) 。

Schedules --

指定何时何种频率来运行job，可以被不同的job重复的利用。比如说我有两个job都要求在每天的凌晨4点运行，那么两个job可以同时指定相同的schedule。

Jobs --

具体的作业的名称，依赖于那个program、schedule。也可以不指定program、schedule，而直接写入program、schedule的相应参数。

Chains -- 把一组program像一个单独的实体连接在一起，作为一个对象。Job可以指向Chains代替单一的program

。这样就能完成类似如下功能：运行A，然后运行B，当A、B都运行成功则运行C，否则运行D。

例子

--创建过程,向test表中写数据

create or replace procedure

scheduler_test

as

id number;

begin

select count(*) into id from

test;

insert into test values

(id,sysdate);

commit;

end;

-- 创建一个schedulers,sys用户

begin

dbms_scheduler.create_schedule(schedule_name

=> 'mytestschedule',repeat_interval =>

'FREQ=MINUTELY;INTERVAL=1');

end;

以下给出部分repeat_interval的写法实例，以供参考

EVERY

DAY 'FREQ=DAILY;INTERVAL=1'

EVERY

MINUTE 'FREQ=MINUTELY;INTERVAL=1'

EVERY 4 HOURS ON THE

HOUR 'FREQ=HOURLY;INTERVAL=4;BYMINUTE=0;BYSECOND=0'

1st DAY OF EVERY

MONTH 'FREQ=DAILY;BYMONTHDAY=1'

WEEKDAYS AT 7am and

3pm 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15;

BYMINUTE=0;BYSECOND=0'

-- 查看schedulers

select * from user_scheduler_schedules

-- 删除schedulers

begin

dbms_scheduler.drop_schedule('MYTESTSCHEDULE');

end;

-- 创建一个scheduler program

begin

dbms_scheduler.create_program(program_name

=> 'mytestprogram',

program_type =>

'STORED_PROCEDURE',

program_action =>

'test.scheduler_test',

enabled => true,

number_of_arguments => 0);

end;

program_type有三类

'PLSQL_BLOCK','STORED_PROCEDURE','EXECUTABLE'分别表示program_action为PLSQL块、存储过程、外部操作系统程序。number_of_arguments表需要传入的参数个数

-- 删除schedulers programs

select * From user_scheduler_programs

begin

dbms_scheduler.drop_program('mytestprogram');

end;

-- 创建job class

begin

dbms_scheduler.create_job_class(job_class_name =>

'mytestjobclass',resource_consumer_group => 'LOW_GROUP');

end;

-- 查询resource consumer groups

select * From dba_rsrc_consumer_groups

-- 查询job_class

select * From dba_scheduler_job_classes

-- 删除job_class

begin

dbms_scheduler.drop_job_class(job_class_name =>

'mytestjobclass');

end;

-- 创建一个job

begin

dbms_scheduler.create_job(job_name =>

'mytestjob',schedule_name => 'MYTESTSCHEDULE',program_name =>

'MYTESTPROGRAM',enabled =>

true);  立即运行

end;

DBMS_SCHEDULER.CREATE_JOB中有一个参数auto_drop，如果设置成TRUE，job执行完成或者job变成disabled则job自动被删除。如下三种情况job被认为是completed

1) Its end date (or its schedule's end date) has

passed.

2) It has runmax_runsnumber

of times.max_runsmust

be set withSET_ATTRIBUTE.

3) It is not a repeating job and has run once.

-- 查看job

select * from user_scheduler_jobs

-- 删除job

begin

dbms_scheduler.drop_job('mytestjob');

end;

-- 停止一个job

begin

dbms_scheduler.disable(name =>

'mytestjob');

end;

设置Repeat Interval

Job和Schedule中REPEAT_INTERVAL参数都是用来控制执行的频率或周期，虽然说周期是一个时间性概念，不过REPEAT_INTERVAL指定的时候并不是一个时间值，而是由一组关键字描述的时间。

除了前面介绍Job和Schedule的REPEAT_INTERVAL参数时，提到该参数拥有FREQ以及INTERVAL两个关键字，其实除此之外，还有如BYMONTH、BYWEEKNO、BYYEARDAY、BYDATE等等参数，可以用来进行更精确的定义，比如通过BYMONTH关键字指定调度运行的月份，BYDAY指定调度在哪天运行等等。

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

注意：SCHEDULER中的REPEAT_INTERVAL也完全可以按照以前Job方式设置， REPEAT_INTERVAL实际上是指定周期，直接指定一个时间值，当然也是周期。

比如说，设置任务每天执行一次，也可以设置REPEAT_INTERVAL参数值如下：

REPEAT_INTERVAL => 'trunc(sysdate)+1'

又比如设置任务每周执行一次：

REPEAT_INTERVAL => 'trunc(sysdate)+7'

不过需要注意，这种方式仅用于创建SCHEDULER中jobs时使用，不能用于schedule。

使用Windows

此处所说的Windows，是指SCHEDULER特性中的一个子项。在SCHEDULER中，WINDOW对应的是一个时间窗口的概念。我们知道普通的jobs是没有运行时间管理地概念的，就是说一个job启动之后，用户只能被动地等待其执行，一直到其执行地任务完成(或DBA手动kill对应进程)，在此期间，执行的job将与其它活动的进程共同竞争当前系统中的资源。

对于大型数据库系统，系统资源那可是相当宝贵的，在9i之前，谁想用就用，谁也管不了，其中表示最甚的就是job。你是否想起了Job Classes，没错定义Job Classes确实可以控制job能够使用的资源，不过单单使用Job Classes并不能灵活的控制job在合适的时间使用适当的资源。

进入10g之后，SCHEDULER中提供了WINDOW，事情终于有了缓解。WINDOW可以指定一个时间窗口，在此期间，通过与Job Classes的搭配组合，能够有效控制job执行时支配(使用)的资源。比如说job通常是在凌晨服务器负载较低时执行，那么就可以通过WINDOW设置在此期间，允许jobs使用更多的系统资源，而到了工作时间后，如果job仍未执行完成，为其分配另一个有限的资源，以尽可能降低job执行占用的资源对其它业务的影响。

１、创建Window

创建window有一个专门的过程：dbms_scheduler.create_window进行处理，该过程有两种调用方式，如下：

--基于SCHEDULE

DBMS_SCHEDULER.CREATE_WINDOW (

window_name IN VARCHAR2,

resource_plan IN VARCHAR2,

schedule_name IN VARCHAR2,

duration IN INTERVAL DAY TO SECOND,

window_priority IN VARCHAR2 DEFAULT 'LOW',

comments IN VARCHAR2 DEFAULT NULL);

--基于定义的调度

DBMS_SCHEDULER.CREATE_WINDOW (

window_name IN VARCHAR2,

resource_plan IN VARCHAR2,

start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,

repeat_interval IN VARCHAR2,

end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,

duration IN INTERVAL DAY TO SECOND,

window_priority IN VARCHAR2 DEFAULT 'LOW',

comments IN VARCHAR2 DEFAULT NULL);

下列几个参数可能需要关注：

(1)Resource_plan：这一参数用来指定要使用的资源使用计划，当打开WINDOW时，就会自动按照指定的资源使用计划中的设置分配资源，当WINDOW关闭时，系统会自动切换回适当资源计划。这个参数在执行过程时甚至可以指定为NULL或空值''，当设置为NULL时，就表示使用默认的资源计划，当设置为空值''时，表示禁用资源使用计划。

(2)Duration：指定WINDOW的有效期，比如说指定为interval '5' hour就表示5个小时，该参数在执行过程时必须指定参数值，否则创建会报错。

(3)Window_priority：该参数用来指定WINDOW的优先级。因为在相同时间只有一个WINDOW有效，因此如果在创建WINDOW时发现重叠的情况，ORACLE就需要根据这一参数指定的规则，来确定优先级，说白了就是先把资源给谁用，这一参数有两个可选值：HIGH或LOW，默认值为LOW。

正如前面CREATE_WINDOW过程语法结构显示的那样，调用该过程有两种方式，差异就在于是指定现有定义好的调度SCHEDULE，还是在执行过程时指定调度，目标和实现的功能都是相同的