导出oracle调度任务,使用Oracle调度程序自动完成任务

实验演示准备：

--业务用户jingyu创建测试表t_timesconn jingyu/jingyucreate table t_times(col1 date);create table t_sum(col1 date, col2 number);create table t_times_old as select * from t_times where 1=2;--查看当前t_times表的记录数select count(*) from t_times;--查看t_sum表的记录信息select * from t_sum;--查看调度相关参数和视图：show parameter JOB_QUEUE_PROCESSESshow parameter RESOURCE_MANAGER_PLANset linesize 140 pagesize 30col state for a15col job_name for a30col owner for a15col job_style for a30col job_type for a30--查询作业select OWNER, JOB_NAME, JOB_STYLE, JOB_TYPE, STATE from DBA_SCHEDULER_JOBS;col program_name for a30col program_type for a30--查询程序计划select OWNER, PROGRAM_NAME, PROGRAM_TYPE, ENABLED, MAX_RUNS from DBA_SCHEDULER_PROGRAMS;

1. 创建作业、计划和时间表

1.1 作业

--CREATE_JOB(创建作业)--作业功能：每秒向T_TIMES表插入当前系统时间，运行1w次后终止。BEGINsys.dbms_scheduler.create_job( job_name => ''JINGYU'.'J_INSERT'',job_type => 'PLSQL_BLOCK',job_action => 'begin -- Insert PL/SQL code here insert into t_times values(sysdate); commit;end;',repeat_interval => 'FREQ=SECONDLY',start_date => to_timestamp_tz('2015-12-09 05:05:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),job_class => ''DEFAULT_JOB_CLASS'',comments => 'Insert the current date into the T_TIMES table.',auto_drop => FALSE,enabled => FALSE);sys.dbms_scheduler.set_attribute( name => ''JINGYU'.'J_INSERT'', attribute => 'max_runs', value => 10000); END;/--ENABLE(启动作业)exec dbms_scheduler.enable(''JINGYU'.'J_INSERT'');--DISABLE(禁用作业)exec dbms_scheduler.disable(''JINGYU'.'J_INSERT'');--DROP_JOB(删除作业)exec dbms_scheduler.drop_job(''J_INSERT'');

1.2 调度程序计划

--CREATE_PROGRAM(创建调度程序计划)BEGINDBMS_SCHEDULER.CREATE_PROGRAM(program_name=>''JINGYU'.'P_SAVEDATE'',program_action=>'declare v_sum number;begin -- Insert PL/SQL code here select count(1) into v_sum from t_times; insert into t_sum values(sysdate,v_sum); commit;end;',program_type=>'PLSQL_BLOCK',number_of_arguments=>0,comments=>'Insert the current date and the count of T_TIMES into the T_SUM.',enabled=>FALSE);END;/--启用程序计划exec dbms_scheduler.enable(''JINGYU'.'P_SAVEDATE'');--停用程序计划exec dbms_scheduler.disable(''JINGYU'.'P_SAVEDATE'');--删除程序计划exec dbms_scheduler.drop_program(''JINGYU'.'P_SAVEDATE'');

注意：计划与作业共享相同的名称空间；后面的时间表和窗口亦是如此。

一个时间表可以应用于许多作业；一个计划可以被许多作业调用。

1.3 时间表

--CREATE_SCHEDULE(创建时间表)BEGINsys.dbms_scheduler.create_schedule( repeat_interval => 'FREQ=MINUTELY',start_date => systimestamp at time zone 'Asia/Shanghai',comments => 'Time: 1/min',schedule_name => ''JINGYU'.'S_PER_MINUTE'');END;/

2. 创建轻量级作业

创建并运行轻量级作业

--依据1.2中程序计划(必须已启用)为模板，创建轻量级作业begin dbms_scheduler.create_job( job_name => 'lightweight_job_1', program_name => 'JINGYU.P_SAVEDATE', job_style => 'LIGHTWEIGHT', comments => 'summarize the count of t_times now!' );end;/--运行轻量级作业exec dbms_scheduler.run_job('lightweight_job_1');--连续运行100次轻量级作业begin for i in 1..100 loop dbms_scheduler.run_job('lightweight_job_1'); end loop;end; /--删除轻量级作业exec dbms_scheduler.drop_job('lightweight_job_1');

3. 使用作业链执行一系列相关任务

--CREATE_PROGRAM(先创建调度程序计划P_INSERT)BEGINDBMS_SCHEDULER.CREATE_PROGRAM(program_name=>''JINGYU'.'P_INSERT'',program_action=>'begin -- Insert PL/SQL code here insert into t_times_old select * from t_times; commit; execute immediate ''truncate table t_times'';end;',program_type=>'PLSQL_BLOCK',number_of_arguments=>0,comments=>'Insert into t_times_old from t_times and truncate table t_times.',enabled=>TRUE);END;/--删除PROGRAMexec DBMS_SCHEDULER.DROP_PROGRAM('P_INSERT');

3.1 创建链对象，定义链步骤，定义链规则

BEGIN --创建链 sys.dbms_scheduler.create_chain( comments => 'savedata to t_sum and insert data into t_times_old', chain_name => ''JINGYU'.'CHAIN1''); --定义链步骤S1 sys.dbms_scheduler.define_chain_step( chain_name => ''JINGYU'.'CHAIN1'', step_name => ''S1'', program_name => ''JINGYU'.'P_SAVEDATE''); --定义链步骤S2 sys.dbms_scheduler.define_chain_step( chain_name => ''JINGYU'.'CHAIN1'', step_name => ''S2'', program_name => ''JINGYU'.'P_INSERT''); --定义链规则C1_RULE1 sys.dbms_scheduler.define_chain_rule( chain_name => ''JINGYU'.'CHAIN1'', condition => 'TRUE', rule_name => 'C1_RULE1', action => 'START S1', comments => 'start the chain'); --定义链规则C1_RULE2 sys.dbms_scheduler.define_chain_rule( chain_name => ''JINGYU'.'CHAIN1'', condition => 'S1 COMPLETED', rule_name => 'C1_RULE2', action => 'START S2');END;/--删除链exec dbms_scheduler.drop_chain(chain_name => ''JINGYU'.'CHAIN1'');--强制删除链BEGINDBMS_SCHEDULER.DROP_CHAIN ( chain_name => ''JINGYU'.'CHAIN1'', force => TRUE);END;/

3.2 启用、运行、监控链

--启用链exec dbms_scheduler.enable('CHAIN1');--先验证当前数据情况select * from t_sum order by col1;select count(1) from t_times_old;select count(1) from t_times;--立即运行链，从S1开始BEGINdbms_scheduler.run_chain(chain_name => 'CHAIN1',job_name => 'J_Chain1_S1_now',start_steps => 'S1');END;/--立即运行链，指定从S2开始BEGINdbms_scheduler.run_chain(chain_name => 'CHAIN1',job_name => 'J_Chain1_S2_now',start_steps => 'S2');END;/--结果：从S1开始，根据定义的规则，S1完成后运行S2; 而指定从S2开始就不会运行S1.--停止上面两个JOB(这两个停止了默认就drop了)exec DBMS_SCHEDULER.STOP_JOB('J_CHAIN1_S1_NOW');exec DBMS_SCHEDULER.STOP_JOB('J_Chain1_S2_now');--创建定时运行链CHAIN1的作业J_chain1BEGINDBMS_SCHEDULER.CREATE_JOB ( job_name => 'J_chain1', job_type => 'CHAIN', job_action => 'CHAIN1', repeat_interval => 'freq=daily;byhour=14;byminute=0;bysecond=0', comments => 'A job to run chain1', enabled => TRUE);END;/--停止JOB(停止会恢复JOB调度状态)exec DBMS_SCHEDULER.STOP_JOB('J_CHAIN1');--删除JOBexec DBMS_SCHEDULER.DROP_JOB('J_CHAIN1');--监视作业链select owner, chain_name, enabled, comments from dba_scheduler_chains;

4. 创建窗口和作业类

--创建窗口BEGINDBMS_SCHEDULER.CREATE_WINDOW(window_name=>''NOON_WINDOW'',resource_plan=>'DEFAULT_MAINTENANCE_PLAN',start_date=>to_timestamp_tz('2015-12-22 Asia/Shanghai', 'YYYY-MM-DD TZR'),duration=>numtodsinterval(60, 'minute'),repeat_interval=>'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',end_date=>null,window_priority=>'LOW',comments=>'Task running over the lunch hour every day.');END;/--创建作业类BEGINsys.dbms_scheduler.create_job_class( logging_level => DBMS_SCHEDULER.LOGGING_RUNS,log_history => 60,resource_consumer_group => 'LOW_GROUP',comments => 'Jobs for test',job_class_name => ''TEST JOBS'');END;/

5. 使用高级调度程序概念确定作业优先顺序

5.1 使用作业类

--作业'J_INSERT'添加到作业类'TEST JOBS'exec dbms_scheduler.set_attribute(name=>'J_INSERT', attribute=>'job_class', value=>''TEST JOBS'');--设置作业'J_INSERT'优先级为1exec dbms_scheduler.set_attribute(name=>'J_INSERT', attribute=>'job_priority', value=>1);--注：作业优先级的值1~5，1的优先级最高。--作业类可以控制日志记录级别--查询DBA_SCHEDULER_JOB_LOG视图select * from DBA_SCHEDULER_JOB_LOG;select job_name, log_date, status from DBA_SCHEDULER_JOB_LOG;--查询DBA_SCHEDULER_JOB_RUN_DETAILS视图select * from DBA_SCHEDULER_JOB_RUN_DETAILS;

5.2 使用窗口

如果关闭窗口时，窗口中尚存在正在运行的作业，默认作业会继续运行完。

--查询作业信息set linesize 200col owner for a30col job_name for a35select owner, job_name, stop_on_window_close, job_priority from dba_scheduler_jobs;--设置stop_on_window_close属性(This attribute only applies if the schedule of a job is a window or a window group.)--a.先把作业'J_INSERT'设置为窗口'NOON_WINDOW'BEGINsys.dbms_scheduler.set_attribute_null( name => ''JINGYU'.'J_INSERT'', attribute => 'start_date'); sys.dbms_scheduler.set_attribute_null( name => ''JINGYU'.'J_INSERT'', attribute => 'repeat_interval'); sys.dbms_scheduler.set_attribute_null( name => ''JINGYU'.'J_INSERT'', attribute => 'end_date'); sys.dbms_scheduler.set_attribute( name => ''JINGYU'.'J_INSERT'', attribute => 'schedule_name', value => ''SYS'.'NOON_WINDOW''); END;/--b.设置作业'J_INSERT'的属性stop_on_window_closeBEGINsys.dbms_scheduler.disable( ''JINGYU'.'J_INSERT'' ); sys.dbms_scheduler.set_attribute( name => ''JINGYU'.'J_INSERT'', attribute => 'stop_on_window_close', value => TRUE); sys.dbms_scheduler.enable( ''JINGYU'.'J_INSERT'' ); END;/

ReferenceOCP 认证考试指南 (1Z0-053)[M]. 清华大学出版社, 2010.