Oracle 自动任务（Job, Scheduler）

已索引

基本 Scheduler

Program ------> Job <------ Schedule (Time, Event)

创建 Program（形式1：PL/SQL匿名块）

BEGIN 
DBMS_SCHEDULER.CREATE_PROGRAM( 
program_name    =>'cy_program', 
program_type    =>'PLSQL_BLOCK',
program_action =>'begin 
                 insert into cyt values(sysdate); 
                 commit; 
                  end;', 
number_of_arguments =>0, 
comments    =>'demo of program', 
enabled    =>TRUE); 
END; 
/

创建 Program（形式2：STORED_PROCEDURE）

BEGIN 
DBMS_SCHEDULER.CREATE_PROGRAM( 
program_name    =>'cy_program', 
program_action =>'cy_procedure', 
program_type    =>'STORED_PROCEDURE', 
number_of_arguments =>0, 
comments    =>'demo for procedure program', 
enabled    =>TRUE); 
END; 
/

cy_procedure 是存储过程名。

创建 Program（形式3：EXECUTABLE）

BEGIN 
DBMS_SCHEDULER.CREATE_PROGRAM( 
program_name    =>'cy_program', 
program_action =>'/home/oracle/backup_spfile.sh', 
program_type    =>'EXECUTABLE', 
number_of_arguments =>0, 
comments    =>'demo for procedure script', 
enabled    =>TRUE); 
END; 
/

注意：需要使用 sys 用户来创建该 job

创建 Schedule

BEGIN 
dbms_scheduler.create_schedule( 
schedule_name    => 'cy_schedule',
start_date   => sysdate, 
repeat_interval => 'FREQ=SECONDLY;INTERVAL=60', 
comments    => 'Started Every 60 seconds'
); 
END; 
/

创建 Job

BEGIN 
dbms_scheduler.create_job( 
job_name    => 'cy_job', 
program_name=> 'cy_program', 
schedule_name    => 'cy_schedule', 
comments=> 'run cy_progran with cy_schedule', 
enabled=> TRUE); 
END;  
/

创建成功后，job会自动运行起来。

硬编码

可以将代码和时间直接写在 job 中：

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name=>'cy_job',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/backup_spfile.sh',
start_date=> SYSDATE,
repeat_interval => 'FREQ=DAILY;BYHOUR=23',
comments => 'Nightly backups');
END;
/

管理 Job

查看作业：

SQL> col JOB_NAME for a30;
SQL> select JOB_NAME,STATE from USER_SCHEDULER_JOBS;

JOB_NAME               STATE
------------------------------ --------------------
CY_JOB                SCHEDULED

禁用作业：

exec dbms_scheduler.disable('cy_job');

删除作业：

exec dbms_scheduler.drop_job('cy_job');

删除程序：

exec dbms_scheduler.drop_program('cy_program');

删除 schedule：

exec dbms_scheduler.drop_schedule('cy_schedule');

Advanced Scheduler Features

advanced scheduler features.jpg