dbms_scheduler简介

1 Program
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name IN VARCHAR2,
program_type IN VARCHAR2,
program_action IN VARCHAR2,
number_of_arguments IN PLS_INTEGER DEFAULT 0,
enabled IN BOOLEAN DEFAULT FALSE,
comments IN VARCHAR2 DEFAULT NULL);

dbms_scheduler.create_program用于创建program
program_name表示的program的名称
program_type表示的program的类型，有PLSQL_BLOCK、STORED_PROCEDURE和EXECUTABLE三种取值，oracle官档对其的解释如下：
? 'PLSQL_BLOCK'
This specifies that the program is a PL/SQL block. Job or program arguments are not supported when the job or program type is PLSQL_BLOCK. In this case, the number of arguments must be 0.
? 'STORED_PROCEDURE'
This specifies that the program is a PL/SQL or Java stored procedure, or an external C subprogram. Only procedures, not functions with return values, are supported. PL/SQL procedures with INOUT or OUT arguments are not supported.
? 'EXECUTABLE'
This specifies that the program is external to the database. External programs imply anything that can be executed from the operating system command line. AnyData arguments are not supported with job or program type EXECUTABLE.

Number_of_arguments表示的是参数的总数
Enabled表示program是否可用，默认不可用
Comments表示的program的解释

SQL> create or replace procedure proc01 as
2 begin
3 for i in 1..1000 loop
4 insert into t_xiaoyu01 values(i);
5 end loop;
6 commit;
7 end;
8 /

Procedure created.

SQL> exec dbms_scheduler.create_program(program_name=>'my_program01',program_typ
e=>'STORED_PROCEDURE',program_action=>'PROC01',number_of_arguments=>0,enabled=>T
RUE,comments=>'my_program01 test');

PL/SQL procedure successfully completed.

这里就创建了一个my_program01的program，然后运行的是PROC01的STORED_PROCEDURE。

DBMS_SCHEDULER.DROP_PROGRAM (
program_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE);

If force is set to FALSE, the program must not be referenced by any job, otherwise an error occurs. If force is set to TRUE, all jobs referencing the program are disabled before the program is dropped.Running jobs that point to the program are not affected by the DROP_PROGRAM call and are allowed to continue.

也可以用dbms_scheduler.drop_program来对指定的program进行删除，其中这个存储过程有个重要的参数就是force，如果设置force=true，program相关联的job也会被置为disable，如果设置force=false，而这个program又有相关联的job，那么这个program将无法删除。

SQL> execute dbms_scheduler.drop_program(program_name=>'my_program01',force=>fal
se);
PL/SQL procedure successfully completed.

1 创建有参数的procedure
SQL> create or replace procedure proc02(name number)
2 as
3 begin
4 insert into t_xiaoyu02 values(name);
5 commit;
6 end;
7 /
Procedure created.

SQL> exec dbms_scheduler.create_program(program_name=>'my_program02',program_typ
e=>'STORED_PROCEDURE',program_action=>'PROC02',number_of_arguments=>1,enabled=>f
alse,comments=>'my_program02 test');
PL/SQL procedure successfully completed.
注意的是创建带有参数的program时需要将enabled=false才能创建成功

Procedure created.

PROCEDURE define_program_argument(
program_name IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_name IN VARCHAR2 DEFAULT NULL,
argument_type IN VARCHAR2,
default_value IN VARCHAR2,
out_argument IN BOOLEAN DEFAULT FALSE);

SQL> execute dbms_scheduler.define_program_argument(program_name=>'my_program02'
,argument_position=>1,argument_type=>'VARCHAR2',default_value=>'program');

PL/SQL procedure successfully completed.

当然我们也可以用dbms_scheduler.drop_program_argument删除参数
Drops a program argument by position:
DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT (
program_name IN VARCHAR2,
argument_position IN PLS_INTEGER);
Drops a program argument by name:
DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT (
program_name IN VARCHAR2,
argument_name IN VARCHAR2);

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

SQL> execute dbms_scheduler.create_schedule(schedule_name=>'my_scheduler02',star
t_date=>sysdate,repeat_interval=>'FREQ=MINUTELY;INTERVAL=1',comments=>'my_schedu
ler01 test');

PL/SQL procedure successfully completed.

这里用dbms_scheduler.create_schedule创建了my_scheduler01的调度，首次运行时间是当前，然后每一分钟运行一次。

可以用dbms_scheduler.drop_scheduler删除调度任务。
DBMS_SCHEDULER.DROP_SCHEDULE (
schedule_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE);

再来看看job的创建和删除
Creates a job in a single call without using an existing program or schedule:
DBMS_SCHEDULER.CREATE_JOB (
job_name IN VARCHAR2,
job_type IN VARCHAR2,
job_action IN VARCHAR2,
number_of_arguments IN PLS_INTEGER DEFAULT 0,
start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
repeat_interval IN VARCHAR2 DEFAULT NULL,
end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
enabled IN BOOLEAN DEFAULT FALSE,
auto_drop IN BOOLEAN DEFAULT TRUE,
comments IN VARCHAR2 DEFAULT NULL);

这里可以不需要program和scheduler直接创建job，其中包括job执行的程序，job调度时间等

Creates a job using a named schedule object and a named program object:
DBMS_SCHEDULER.CREATE_JOB (
job_name IN VARCHAR2,
program_name IN VARCHAR2,
schedule_name IN VARCHAR2,
job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
enabled IN BOOLEAN DEFAULT FALSE,
auto_drop IN BOOLEAN DEFAULT TRUE,
comments IN VARCHAR2 DEFAULT NULL);

这里创建了一个有指定调度和程序的job，这个job运行的脚本存储在program中，而job的调度存储在scheduler中，这种创建job的方式，仅仅只是将program和scheduler关联起来到一个job中去。

Creates a job using a named program object and an inlined schedule:
DBMS_SCHEDULER.CREATE_JOB (
job_name IN VARCHAR2,
program_name IN VARCHAR2,
start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
repeat_interval IN VARCHAR2 DEFAULT NULL,
end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
enabled IN BOOLEAN DEFAULT FALSE,
auto_drop IN BOOLEAN DEFAULT TRUE,
comments IN VARCHAR2 DEFAULT NULL);

这里创建了有指定program的job，其中job的运行程序是存储在program中，而job的调度在创建时刻直接声明了。

Creates a job using a named schedule object and an inlined program:
DBMS_SCHEDULER.CREATE_JOB (
job_name IN VARCHAR2,
schedule_name IN VARCHAR2,
job_type IN VARCHAR2,
job_action IN VARCHAR2,
number_of_arguments IN PLS_INTEGER DEFAULT 0,
job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
enabled IN BOOLEAN DEFAULT FALSE,
auto_drop IN BOOLEAN DEFAULT TRUE,
comments IN VARCHAR2 DEFAULT NULL);

这里创建的有指定调度名称的job，其中job的调度是存储在指定的scheduler中，而job的运行程序则在创建时候直接声明。

利用dbms_scheduler.create_job的创建有六种，这里只列出上述四种，更详细的可以去参考官方文档。

还可以用dbms_scheduler.create_job_class来创建job类，把相同类型的job都包装到job class中去，然后对应相应的资源管理组成员。
DBMS_SCHEDULER.CREATE_JOB_CLASS (
job_class_name IN VARCHAR2,
resource_consumer_group IN VARCHAR2 DEFAULT NULL,
service IN VARCHAR2 DEFAULT NULL,
logging_level IN PLS_INTEGER
DEFAULT DBMS_SCHEDULER.LOGGING_RUNS,
log_history IN PLS_INTEGER DEFAULT NULL,
comments IN VARCHAR2 DEFAULT NULL);

也可以用dbms_scheduler.drop_job来删除指定的job
DBMS_SCHEDULER.DROP_JOB (
job_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE);

这里小鱼只展示上述创建job的两种方式：
SQL> execute dbms_scheduler.create_job(job_name=>'my_job01',job_type=>'STORED_PR
OCEDURE',job_action=>'proc01',start_date=>sysdate,repeat_interval=>'sysdate+1/14
40',enabled=>true);

PL/SQL procedure successfully completed.

SQL> execute dbms_scheduler.create_job(job_name=>'my_job02',program_name=>'my_pr
ogram02',schedule_name=>'my_scheduler01',enabled=>true)

PL/SQL procedure successfully completed.

感觉这里确实不太好理解，dbms_scheduler有job、scheduler、program三类，这里小鱼的个人理解就是，oracle既可以dbms_scheduler.create_job的方式直接创建job，而不需要去跟这个job指定对应的scheduler和program，也可以先创建对应的scheduler和program，然后创建job时跟其对应起来，跟之前的dbms_job创建job的方式相比，可以单独分离job运行的程序和job的调度。

我们在oracle 10g中就有了oracle的自动分析的任务，但是关于这个任务是如何通过dbms_scheduler的program、job、scheduler和window运行了解的并不深入，接下来我们先介绍下window的创建再结合oracle 10g的自动分析job来看看oracle是如何规划这个自动分析的任务。

Creates a window using a named schedule object:
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);

这里创建window是先根据指定的scheduler_name来创建的。

Creates a window using an inlined schedule:
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);

这里创建window是没有用指定的scheduler_name来创建。

那么window究竟是什么了，我们看看oracle官档给出的解释：
This procedure creates a recurring time window and associates it with a resource plan. The window can then be used to schedule jobs, which run under the associated resource plan.

这个其实就是比scheduler多了一个resource plan的功能，也就是结合了资源管理方面的东西，换句话说window就是对指定的scheduler规划了资源调度信息。

DBMS_SCHEDULER.CREATE_WINDOW_GROUP (
group_name IN VARCHAR2,
window_list IN VARCHAR2 DEFAULT NULL,
comments IN VARCHAR2 DEFAULT NULL);

还可以用dbms_scheduler.create_window_group将部分的window包装成一个group。

那么job能否和window或者window group联合起来了，我们看看官档中dbms_scheduler.create_job中对于scheduler_name的描述就清楚：
schedule_name ：The name of the schedule, window, or window group associated with this job.

Window和window group和job相比scheduler结合job的优点是，window group下可以有多组window，也就是多个调度的任务窗口，然后去对应一个job，那么job在不同的时间段将有不同的调度任务，oracle 10gR2的自动分析就是如此规划的。

我们以oracle 10gR2来看看job，job_class、scheduler，program，window和window group是如何关联的：

SQL> select owner,program_name,job_class,schedule_name,schedule_type from dba_sc
heduler_jobs where job_name='GATHER_STATS_JOB';
OWNER PROGRAM_NAME JOB_CLASS SCHEDULE_NAME SCHEDULE_TYPE
---------- ------------------------------ -------------------- -----------------
-------------
SYS GATHER_STATS_PROG AUTO_TASKS_JOB_CLASS MAINTENANCE_WINDOW_GROUP WINDOW_GROUP

然后查看program运行的具体的程序名称和类型
SQL> select program_type,program_action from dba_scheduler_programs where progra
m_name='GATHER_STATS_PROG';

PROGRAM_TYPE PROGRAM_ACTION
---------------- --------------------------------------------------
STORED_PROCEDURE dbms_stats.gather_database_stats_job_proc

由于这里scheduler_type是window group，再来查看这个window group的调度任务：
SQL> select number_of_windows,enabled from dba_scheduler_window_groups where win
dow_group_name='MAINTENANCE_WINDOW_GROUP';
NUMBER_OF_WINDOWS ENABL
----------------- -----
2 TRUE

我们再来看看这个window group各个window详细的调度安排。
SQL> select window_name from dba_scheduler_wingroup_members where window_group_
name='MAINTENANCE_WINDOW_GROUP';

WINDOW_NAME
------------------------------
WEEKNIGHT_WINDOW
WEEKEND_WINDOW

SQL> select window_name,repeat_interval,next_start_date,last_start_date from DBA
_SCHEDULER_WINDOWS where window_name in ('WEEKNIGHT_WINDOW','WEEKEND_WINDOW');

WINDOW_NAME REPEAT_INTERVAL
NEXT_START_DATE
------------------------------ -------------------------------------------------
- ---------------------------------------------------------------------------
LAST_START_DATE
---------------------------------------------------------------------------
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;by
m 06-AUG-14 10.00.00.500000 PM +08:00
inute=0; bysecOnd=0
05-AUG-14 10.00.00.195000 PM +08:00

WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond
= 09-AUG-14 12.00.00.000000 AM +08:00
0
02-AUG-14 08.38.32.140000 AM +08:00

再来看看这个job的job class资源管理成员
SQL> select resource_consumer_group from dba_scheduler_job_classes where job_cla
ss_name='AUTO_TASKS_JOB_CLASS';

RESOURCE_CONSUMER_GROUP
------------------------------
AUTO_TASK_CONSUMER_GROUP

可以看出oracle这里是首先创建program保存了运行的程序，然后创建window和window group指定两个时间段的调度的任务，再创建了和program相关的job和job calss，并且指定自动分析的job所属的class的资源管理成员