下包括tu等课件automating tasks with the scheduler

1、11.创建程序周炯 jon.zhoujiong6BEGIN DBMS_SCHEDULER.CREATE_PROGRAM(program_name => 'CALC_STATS2', program_action =>'HR.UPDATE_HR_SCHEMA_STATS',program_type => 'STORED_PROCEDURE' enabled => TRUE);END;/,关键成分和步骤 按以下步骤使用计划任务简化任务管理:1. 创建程序.2. 创建计划任务.3. 创建并提交一份作业4. 作业Program S

2、cheduleJobattributesJob Arguments周炯 jon.zhoujiong5一个简单的 Job周炯 jon houjionggmail com4WHATWHEN简化任务管理在每月月底执当消息等待时当视图更新行一系列月底任务运行一个队列程序时表中数据每天备份数据库一天2次计 算表和索引统计当文件到达时开启加载每小时生成一当当前的索引结个报告束重建时开启重建周炯 jon zhoujionggmail com3目标 课程目标: 用计划任务来简化任务管理 创建作业，程序，计划任务 监测事务执行 用基于时间或者基于的进度表执行一个计划事务 用作业链来执行一系列相关任务 用高级计划

3、任务概念来作业执行顺序周炯 jon.zhoujiong2Key Comp. & Steps Schedules Job ChainsAdv.Concepts用计划任务自动化任务周炯 jon.zhoujoing12Creating a Time-Based Job Full Notes Page周炯 jon.zhoujiong12创建基于时间的作业 案例:创建一个每天晚上11点唤醒备份的作业周炯 jon.zhoujiong11BEGIN DBMS_SCHEDULER.CREATE_JOB(job_name=>'HR.DO_BACKUP', job_type =>

4、; 'EXECUTABLE',job_action => '/home/usr/dba/rman/nightly_incr.sh', start_date=> SYSDATE, repeat_interval=>'FREQ=DAILY;BYHOUR=23',/* next night at 11:00 PM */ comments => 'Nightly incremental backups');END;/使用基于时间或者基于的计划任务周炯 jon houjionggmail com10ScheduleT

5、imeEventKey Comp. & Steps> Schedules Job Chains Adv.Concepts4.作业SELECT job_name, status, error#, run_duration FROM USER_SCHEDULER_JOB_RUN_DETAILS;JOB_NAMESTATUS ERROR# RUN_DURATION GATHER_STATS_JOB SUCCESS0 +000 00:08:20PART_EXCHANGE_JOB FAILURE 6576 +000 00:00:00周炯 jon zhoujionggmail com93.

6、创建并运行一个作业周炯 jon.zhoujiong82.创建并使用计划任务周炯 jon.zhoujiong7BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'stats_schedule', start_date => SYSTIMESTAMP,end date => SYSTIMESTAMP + 30,comments => 'Every hour'); END;/repeat_interval => 'FREQ=HOURLY;INTERVAL=1',3创

7、建复合计划任务INCLUDEEXCLUDEINTERSECT周炯 jon.zhoujiong18Events Raised by the Scheduler Full Notes Page周炯 jon.zhoujiong17基于的计划任务 类型: 用户生成或应用程序生成 计划任务生成 计划任务启动的 JOB_START JOB_SCH_LIM_REACHED JOB_SUCCEEDED JOB_DISABLED JOB_FAILED JOB_CHAIN_STALLED JOB_BROKEN JOB_ALL_EVENTSJOB COMPLETED提高的例子:周炯 jon houjionggmai

8、l com16DBMS_SCHEDULER.SET_ATTRIBUTE('hr.do_backup','raise_events', DBMS_SCHEDULER.JOB_FAILED);创建基于的作业 案例:创建一个作业，如果在9点之前文件系统，则启动加载器周炯 jon zhoujionggmail com15BEGIN DBMS_SCHEDULER.CREATE_JOB(job_name=>'ADMIN.PERFORM_DATA_LOAD',job_type => 'EXECUTABLE',job_action =

9、> '/home/usr/dba/rman/report_failure.sh', start date => SYSTIMESTAMP,END;event_condition => 'tab.user_data.object_owner = ''HR'' and tab.user_data.object_name = ''DATA.TXT'' and tab.user_data.event_type = ''FILE_ARRIVAL''and tab.us

10、er_data.event_timestamp < 9 ', queue_spec => 'HR.LOAD_JOB_EVENT_Q');用企业级管理器创建基于的计划任务周炯 jon.zhoujiong14创建基于计划任务 要创建基于计划任务, 你应该设定: 队列说明书 (在何处把消息排队来启动你的作业) (和Oracle Stream AQ规则相同语法) 启动作业为TRUE的条件Oracle Database 10gEvent ADTApplication(抽象数据类型)Queue周炯 jon.zhoujiong13Scheduler43.定义链步骤周炯 j

11、on.zhoujiong242.定义链步骤周炯 jon.zhoujiong23DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (1chain_name => 'bulk_load_chain',step_name => 'load_data_evt',event_condition => 'tab.user_data.object_owner = ''HR'' and tab.user_data.object_name = ''DATA.TXT'

12、9; and tab.user_data.event_type = ''FILE_ARRIVAL'' ',queue_spec => 'HR.LOAD_JOB_EVENT_Q');DBMS_SCHEDULER.DEFINE_CHAIN_STEP (2chain_name => 'bulk_load_chain',step_name => 'do_bulk_load', program_name => 'hr.load_data_prog);DBMS_SCHEDULER.DEF

13、INE_CHAIN_STEP (3chain_name => 'bulk_load_chain',step_name => 'rebuild_indx', program_name => 'hr.rebuild_indexes');1.创建作业链对象.周炯 jon houjionggmail com2212345链案例Dependency SchedulingSTARTSchedule周炯 jon zhoujionggmail com21JobLoad_data_evt 12Stop_when_53disk_full_evtEN

14、D4BULK_LOAD_CHAINRun_reports(HR.GEN_REPORTS)Do_bulk_loadRebuild_indxCreating Chains Full Notes Page周炯 jon.zhoujiong20创建作业链1. 创建作业链对象.2. 定义链步骤.3. 定义链的规则.4. 开启链: 链. 创建一个指向链的作业.Job chain周炯 jon.zhoujiong19JobKey Comp. & Steps Schedules> Job Chains Adv.Concepts5用一个窗口来决定作业优先级周炯 jon.zhoujiong30 Job

15、Priorit Job1y1 Job22 Job33 Job45 Job52APPL_JOBSOTHERADMIN_JOBSDaytime windowJob5Job4Job3Job2Job1创建窗口 为12月创建一个窗口，方案用END_OF_YEAR，并且在东部时间晚上6点到早上6 点保持活跃周炯 jon.zhoujiong29BEGIN DBMS_SCHEDULER.CREATE_WINDOW(window_name => 'DEC_NIGHTS', resource_plan => 'END_OF_YEAR',start_date =>

16、'01-DEC-03 06.00.00 PM EST',repeat_interval => 'FREQ=DAILY; BYHOUR=18', duration => '0 12:00:00',end_date => '31-DEC-03 06.00.00 AM EST',comments => 'Every day at 6:00 PM'); END;/创建作业类周炯 jon houjionggmail com28EXECUTE DBMS_SCHEDULER.CREATE_JOB_CLAS

17、Sjob_class_name => 'ADMIN_JOBS', -( -resource_consumer_group => 'DAYTIME_JOBS', - logging_level => DBMS_SCHEDULER.LOGGING_OFF);复合计划任务概念ResourceResourceWindow consumer groupplangroupJob chainJob classWindowProgramJobScheduleArgumentsArgumentsTimeEvent周炯 jon zhoujionggmail com

18、27Key Comp. & Steps Schedules Job Chains> Adv.Concepts监视作业链DBA | ALL | USER_SCHEDULER_CHAINSDBA | ALL | USER_SCHEDULER_CHAIN_RULES DBA | ALL | USER_SCHEDULER_CHAIN_STEPS DBA | ALL | USER_SCHEDULER_RUNNING_CHAINS周炯 jon.zhoujiong264.开启链周炯 jon.zhoujiong25BEGINDBMS_SCHEDULER.CREATE_JOB (job_name=> 'bulk_load_chain_job'