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

1、11.創(chuàng)建程序周炯 jon.zhoujiong6BEGIN DBMS_SCHEDULER.CREATE_PROGRAM(program_name => 'CALC_STATS2', program_action =>'HR.UPDATE_HR_SCHEMA_STATS',program_type => 'STORED_PROCEDURE' enabled => TRUE);END;/,關(guān)鍵成分和步驟 按以下步驟使用計劃任務(wù)簡化任務(wù)管理:1. 創(chuàng)建程序.2. 創(chuàng)建計劃任務(wù).3. 創(chuàng)建并提交一份作業(yè)4. 作業(yè)Program S

2、cheduleJobattributesJob Arguments周炯 jon.zhoujiong5一個簡單的 Job周炯 jon houjionggmail com4WHATWHEN簡化任務(wù)管理在每月月底執(zhí)當(dāng)消息等待時當(dāng)視圖更新行一系列月底任務(wù)運行一個隊列程序時表中數(shù)據(jù)每天備份數(shù)據(jù)庫一天2次計 算表和索引統(tǒng)計當(dāng)文件到達時開啟加載每小時生成一當(dāng)當(dāng)前的索引結(jié)個報告束重建時開啟重建周炯 jon zhoujionggmail com3目標(biāo) 課程目標(biāo): 用計劃任務(wù)來簡化任務(wù)管理 創(chuàng)建作業(yè)，程序，計劃任務(wù) 監(jiān)測事務(wù)執(zhí)行 用基于時間或者基于的進度表執(zhí)行一個計劃事務(wù) 用作業(yè)鏈來執(zhí)行一系列相關(guān)任務(wù) 用高級計劃

3、任務(wù)概念來作業(yè)執(zhí)行順序周炯 jon.zhoujiong2Key Comp. & Steps Schedules Job ChainsAdv.Concepts用計劃任務(wù)自動化任務(wù)周炯 jon.zhoujoing12Creating a Time-Based Job Full Notes Page周炯 jon.zhoujiong12創(chuàng)建基于時間的作業(yè) 案例:創(chuàng)建一個每天晚上11點喚醒備份的作業(yè)周炯 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;/使用基于時間或者基于的計劃任務(wù)周炯 jon houjionggmail com10ScheduleT

5、imeEventKey Comp. & Steps> Schedules Job Chains Adv.Concepts4.作業(yè)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、創(chuàng)建并運行一個作業(yè)周炯 jon.zhoujiong82.創(chuàng)建并使用計劃任務(wù)周炯 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創(chuàng)

7、建復(fù)合計劃任務(wù)INCLUDEEXCLUDEINTERSECT周炯 jon.zhoujiong18Events Raised by the Scheduler Full Notes Page周炯 jon.zhoujiong17基于的計劃任務(wù) 類型: 用戶生成或應(yīng)用程序生成 計劃任務(wù)生成 計劃任務(wù)啟動的 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);創(chuàng)建基于的作業(yè) 案例:創(chuàng)建一個作業(yè)，如果在9點之前文件系統(tǒng)，則啟動加載器周炯 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');用企業(yè)級管理器創(chuàng)建基于的計劃任務(wù)周炯 jon.zhoujiong14創(chuàng)建基于計劃任務(wù) 要創(chuàng)建基于計劃任務(wù), 你應(yīng)該設(shè)定: 隊列說明書 (在何處把消息排隊來啟動你的作業(yè)) (和Oracle Stream AQ規(guī)則相同語法) 啟動作業(yè)為TRUE的條件Oracle Database 10gEvent ADTApplication(抽象數(shù)據(jù)類型)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.創(chuàng)建作業(yè)鏈對象.周炯 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創(chuàng)建作業(yè)鏈1. 創(chuàng)建作業(yè)鏈對象.2. 定義鏈步驟.3. 定義鏈的規(guī)則.4. 開啟鏈: 鏈. 創(chuàng)建一個指向鏈的作業(yè).Job chain周炯 jon.zhoujiong19JobKey Comp. & Steps Schedules> Job Chains Adv.Concepts5用一個窗口來決定作業(yè)優(yōu)先級周炯 jon.zhoujiong30 Job

15、Priorit Job1y1 Job22 Job33 Job45 Job52APPL_JOBSOTHERADMIN_JOBSDaytime windowJob5Job4Job3Job2Job1創(chuàng)建窗口 為12月創(chuàng)建一個窗口，方案用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;/創(chuàng)建作業(yè)類周炯 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);復(fù)合計劃任務(wù)概念ResourceResourceWindow consumer groupplangroupJob chainJob classWindowProgramJobScheduleArgumentsArgumentsTimeEvent周炯 jon zhoujionggmail com

18、27Key Comp. & Steps Schedules Job Chains> Adv.Concepts監(jiān)視作業(yè)鏈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'