自动化管理任务

自动化管理任务

体系结构

select program from v$process where program like '%J%';

PROGRAM

ORACLE.EXE (CJQ0)

ORACLE.EXE (J000)

CJQ0 是监视进程，作业开始时被自动启动

Jnnn 作业进程

show parameter job

NAME TYPE VALUE

job_queue_processes integer 10

同时运行作业数

各对象

程序

begin

dbms_scheduler.create_program(program_name => 'prog_insert_t',

program_type => 'plsql_block',

program_action => 'insert into t.t values(1);commit;',

enabled => true);

end;

/

创建一个程序块型的程序

create or replace procedure pro__insert_t (p_num number default 0)

as

begin

insert into t.t values(p_num);

commit;

end;

/

begin

dbms_scheduler.create_program(

program_name => 'prog_pro_insert_t', program_type => 'stored_procedure',

number_of_arguments => 1,program_action => 'pro_insert_t',enabled => false);

dbms_scheduler.define_program_argument(

第 43 页

program_name => 'prog_pro_insert_t',argument_position => 1,

argument_name => 'p_num', argument_type => 'number',default_value => 0, out_argument => false);

dbms_scheduler.enable('prog_pro_insert_t');

end;

/

创建基于包含参数的存储过程时，需要先把程序置为不启用，接着定义参数，最后启用程序

begin

dbms_scheduler.create_program(

program_name => 'prog_ping',program_type => 'executable',

program_action => ' ping 127.0.0.1',enabled => true);

end;

/

创建一个程序，它执行可执行程序

col owner format a10

col program_name format a25

col program_action format a60

select owner,program_name,program_type,program_action,number_of_arguments,enabled

from dba_scheduler_programs where program_name like 'PROG%';

OWNER PROGRAM_NAME PROGRAM_TYPE PROGRAM_ACTION NUMBER_OF_ARGUMENTS ENABL

SYS PROG_PING EXECUTABLE ping 127.0.0.1 0 TRUE

SYS PROG_INSERT_T PLSQL_BLOCK insert into t.t values(1);commit; 0 TRUE

SYS PROG_PRO_INSERT_T STORED_PROCEDURE pro_insert_t 1 TRUE

列出所创建的程序信息

col argument_name format a20

col argument_type format a20

col default_value format a20

select owner,program_name,argument_name,argument_position,argument_type,metadata_attribute,default_value,out_argument

from dba_scheduler_program_args where program_name like 'PROG%';

OWNER PROGRAM_NAME ARGUMENT_NAME ARGUMENT_POSITION ARGUMENT_TYPE METADATA_ATT DEFAULT_VALUE OUT_A

SYS PROG_PRO_CREATE_INSERT_T P_NUM 1 NUMBER 0 FALSE

列出程序的参数信息

注：程序在作业与执行动作之间提供了一个抽象层，一个程序可被多个作业调用

创建用户自己模式内需 CREATE JOB 权限，否则需要 CREATE ANY JOB 权限

调度

begin

dbms_scheduler.create_schedule( schedule_name => 's1',repeat_interval=>'freq=hourly; interval=2');

end;

/

创建一个每隔 2 小时的调度

begin

dbms_scheduler.create_schedule( schedule_name => 's2',repeat_interval=>'freq=hourly; byminute=15');

end;

/

创建一个每小时的第 15 分启动的调度

begin

dbms_scheduler.create_schedule( schedule_name => 's3',repeat_interval=>'freq=hourly;interval=3;byminute=30');

end;

/

创建一个间隔 3 小时，启动时刻在 30 分的调度

begin

dbms_scheduler.create_schedule(

schedule_name => 's4',repeat_interval=>'freq=monthly;bymonth=1,7,12;

bymonthday=17;byhour=13;');

end;

/

创建一个在 1，7 和 12 月的 17 日 13 点启动的调度

col owner format a10

col schedule_name format a10

col repeat_interval format a60

select owner,schedule_name,schedule_type,repeat_interval

第 44 页

from dba_scheduler_schedules where schedule_name like 'S%';

OWNER SCHEDULE_N SCHEDULE REPEAT_INTERVAL

SYS S1 CALENDAR freq=hourly; interval=2

SYS S2 CALENDAR freq=hourly; byminute=15

SYS S3 CALENDAR freq=hourly;interval=3;byminute=30

SYS S4 CALENDAR freq=monthly;bymonth=1,7,12;

bymonthday=17;byhour=13;

列出所创建的调度

注：创建用户自己模式内需 CREATE JOB 权限，否则需要 CREATE ANY JOB 权限

作业类

begin

dbms_scheduler.create_job_class(

job_class_name=>'jc1',resource_consumer_group=>'A',service=>NULL,

logging_level=>dbms_scheduler.logging_runs,log_history=>60);

end;

/

service 选项用于 RAC 中指定服务名，log_history 选项指定作业日志保存时间（单位：天）

begin

dbms_scheduler.create_job_class(

job_class_name=>'jc2',resource_consumer_group=>'C',service=>NULL,

logging_level=>dbms_scheduler.logging_runs,log_history=>10);

end;

/

col job_class_name format a15

col resource_consumer_group format a15

col service format a30

col comments format a50

select job_class_name,resource_consumer_group,service,logging_level,log_history,comments

from dba_scheduler_job_classes where job_class_name like 'JC%';

JOB_CLASS_NAME RESOURCE_CONSUM SERVICE LOGG LOG_HISTORY COMMENTS

JC1 A RUNS 60

JC2 C RUNS 10

列出作业类信息

注：创建作业类需要 MANAGE SCHEDULER 权限，如作业类拥有者与作业执行者不是同一用户，那么作业执行者须有 EXECUTE 作业类的权限