[bbk5177]第62集-第6章 - 用scheduler自动化 10(章节标题内容调整)
Remote database job是11g的新特性
Creating a Job Array
--1、Declare variables of types sys.job and sys.job_array: DECLARE newjob sys.job; newjobarr sys.job_array; --2、Initialize the job array: BEGIN newjobarr := SYS.JOB_ARRAY(); --3、Size the job array to hold the number of jobs needed: newjobarr.EXTEND(100); --4、Place jobs in the job array: FOR i IN 1..100 LOOP newjob:=SYS.JOB( job_name => 'LWTJK' || TO_CHAR(i), job_style => 'LIGHTWEIGHT', job_template => 'MY_PROG', enabled => TRUE ); newjobarr(i) := newjob; END LOOP; --5、Submit the job array as one transaction: DBMS_SCHEDULER.CREATE_JOBS(newjobarr,'TRANSACTIONAL');
Creating Remote Database Jobs
Perform the following tasks to create a remote job:
1、Set up the originating database for remote jobs.
2、Create the job by using DBMS_SCHEDULER.CREATE_JOB.
3、Create a credential by using DBMS_SCHEDULER.CREATE_CREDENTIAL.
4、Set the job CREDENTIAL_NAME attribute by using DBMS_SCHEDULER.SET_ATTRIBUTE.
5、Set the job DESTINATION attribute by using DBMS_SCHEDULER.SET ATTRIBUTE.
6、Enable the job by using DBMS_SCHEDULER.ENABLE.
Viewing Scheduler Meta Data
Major Scheduler management views,displaying:
*_SCHEDULER_JOBS:All jobs,enabled and disabled
*_SCHEDULER_SCHEDULES:All schedules
*_SCHEDULER _PROGRAMS:All programs
*_SCHEDULER_RUNNING_JOBS:Active job states
*_SCHEDULER_JOB_LOG:All job state changes
*_SCHEDULER_JOB_RUN_DETAILS:All completed job runs
SQL> SELECT job_name,status,error#,run_duration FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE rownum BETWEEN 1 AND 100; JOB_NAME STATUS ERROR# RUN_DURATION --------------- --------------- ---------- ------------------------------ JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB_NAME STATUS ERROR# RUN_DURATION --------------- --------------- ---------- ------------------------------ JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB_NAME STATUS ERROR# RUN_DURATION --------------- --------------- ---------- ------------------------------ JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 ADD_DEPT_JOB SUCCEEDED 0 +000 00:00:00 JOB_NAME STATUS ERROR# RUN_DURATION --------------- --------------- ---------- ------------------------------ JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB_NAME STATUS ERROR# RUN_DURATION --------------- --------------- ---------- ------------------------------ JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB_NAME STATUS ERROR# RUN_DURATION --------------- --------------- ---------- ------------------------------ JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB_NAME STATUS ERROR# RUN_DURATION --------------- --------------- ---------- ------------------------------ JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB_NAME STATUS ERROR# RUN_DURATION --------------- --------------- ---------- ------------------------------ JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB_NAME STATUS ERROR# RUN_DURATION --------------- --------------- ---------- ------------------------------ JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB_NAME STATUS ERROR# RUN_DURATION --------------- --------------- ---------- ------------------------------ JOB1 SUCCEEDED 0 +000 00:00:00 100 rows selected.
Summary
In this lesson,you should have learned how to:
- Simplify management tasks by using the Scheduler
- Create a job,program,and schedule
- Monitor job execution
- Use a time-based or event-based schedule for executing Scheduler jobs
- Describe the use of windows,window groups,job classes,and consumer groups
- Use email notification
- Use job chains to perform a series of related tasks
- Describe Scheduler jobs on remote systems
- Use advanced Scheduler concepts to prioritize jobs
