Scheduler in Oracle Database 10g(轉)
Scheduler in Oracle Database 10g
Oracle 10g includes a comprehensive scheduler (DBMS_SCHEDULER) to replace and extend the functionality provided by the DBMS_JOB package. Jobs form the core of the functionality, but there are several other components available:Programs
The scheduler allows you to optionally create programs which hold metadata about a task, but no schedule information. A program may related to a PL/SQL block, a stored procedure or an OS executable file. Programs are created using the CREATE_PROGRAM procedure:Notice how programs that accept arguments must have the arguments defined before they can be enabled.-- Create the test programs. BEGIN -- PL/SQL Block. DBMS_SCHEDULER.create_program ( program_name => 'test_plsql_block_prog', program_type => 'PLSQL_BLOCK', program_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;', enabled => TRUE, comments => 'Program to gather SCOTT''s statistics using a PL/SQL block.'); -- Shell Script. DBMS_SCHEDULER.create_program ( program_name => 'test_executable_prog', program_type => 'EXECUTABLE', program_action => '/u01/app/oracle/dba/gather_scott_stats.sh', number_of_arguments => 0, enabled => TRUE, comments => 'Program to gather SCOTT''s statistics us a shell script.'); -- Stored Procedure with Arguments. DBMS_SCHEDULER.create_program ( program_name => 'test_stored_procedure_prog', program_type => 'STORED_PROCEDURE', program_action => 'DBMS_STATS.gather_schema_stats', number_of_arguments => 1, enabled => FALSE, comments => 'Program to gather SCOTT''s statistics using a stored procedure.'); DBMS_SCHEDULER.define_program_argument ( program_name => 'test_stored_procedure_prog', argument_name => 'ownname', argument_position => 1, argument_type => 'VARCHAR2', default_value => 'SCOTT'); DBMS_SCHEDULER.enable (name => 'test_stored_procedure_prog'); END; / PL/SQL procedure successfully completed. -- Display the program details. SELECT owner, program_name, enabled FROM dba_scheduler_programs; OWNER PROGRAM_NAME ENABL ------------------------------ ------------------------------ ----- SYS PURGE_LOG_PROG TRUE SYS GATHER_STATS_PROG TRUE SYS TEST_PLSQL_BLOCK_PROG TRUE SYS TEST_EXECUTABLE_PROG TRUE SYS TEST_STORED_PROCEDURE_PROG TRUE 5 rows selected.
Programs can be deleted using the DROP_PROGRAM procedure:
BEGIN DBMS_SCHEDULER.drop_program (program_name => 'test_plsql_block_prog'); DBMS_SCHEDULER.drop_program (program_name => 'test_stored_procedure_prog'); DBMS_SCHEDULER.drop_program (program_name => 'test_executable_prog'); END; / PL/SQL procedure successfully completed. -- Display the program details. SELECT owner, program_name, enabled FROM dba_scheduler_programs; OWNER PROGRAM_NAME ENABL ------------------------------ ------------------------------ ----- SYS PURGE_LOG_PROG TRUE SYS GATHER_STATS_PROG TRUE 2 rows selected.
Schedules
Schedules optionally define the start time, end time and interval related to a job. Schedules are created using the CREATE_SCHEDULE procedure:Notice how the interval is defined using the calendaring syntax. A schedule can be dropped using the DROP_SCHEDULE procedure:-- Create the schedule. BEGIN DBMS_SCHEDULER.create_schedule ( schedule_name => 'test_hourly_schedule', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, comments => 'Repeats hourly, on the hour, for ever.'); END; / PL/SQL procedure successfully completed. -- Display the schedule details. SELECT owner, schedule_name FROM dba_scheduler_schedules; OWNER SCHEDULE_NAME ------------------------------ ------------------------------ SYS DAILY_PURGE_SCHEDULE SYS TEST_HOURLY_SCHEDULE 2 rows selected.
BEGIN DBMS_SCHEDULER.drop_schedule (schedule_name => 'TEST_HOURLY_SCHEDULE'); END; / PL/SQL procedure successfully completed. -- Display the schedule details. SELECT owner, schedule_name FROM dba_scheduler_schedules; OWNER SCHEDULE_NAME ------------------------------ ------------------------------ SYS DAILY_PURGE_SCHEDULE 1 row selected.
Jobs
Jobs are what the scheduler is all about. They can either be made up of predefined parts (programs and schedules) or completely self contained depending on which overload of the CREATE_JOB procedure is used to create them:Jobs are normally run asynchronously under the control of the job coordinator, but they can be controlled manually using the RUN_JOB and STOP_JOB procedures:-- Create jobs. BEGIN -- Job defined entirely by the CREATE JOB procedure. DBMS_SCHEDULER.create_job ( job_name => 'test_full_job_definition', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, enabled => TRUE, comments => 'Job defined entirely by the CREATE JOB procedure.'); -- Job defined by an existing program and schedule. DBMS_SCHEDULER.create_job ( job_name => 'test_prog_sched_job_definition', program_name => 'test_plsql_block_prog', schedule_name => 'test_hourly_schedule', enabled => TRUE, comments => 'Job defined by an existing program and schedule.'); -- Job defined by existing program and inline schedule. DBMS_SCHEDULER.create_job ( job_name => 'test_prog_job_definition', program_name => 'test_plsql_block_prog', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, enabled => TRUE, comments => 'Job defined by existing program and inline schedule.'); -- Job defined by existing schedule and inline program. DBMS_SCHEDULER.create_job ( job_name => 'test_sched_job_definition', schedule_name => 'test_hourly_schedule', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;', enabled => TRUE, comments => 'Job defined by existing schedule and inline program.'); END; / PL/SQL procedure successfully completed. -- Display job details. SELECT owner, job_name, enabled FROM dba_scheduler_jobs; OWNER JOB_NAME ENABL ------------------------------ ------------------------------ ----- SYS PURGE_LOG TRUE SYS GATHER_STATS_JOB TRUE SYS TEST_FULL_JOB_DEFINITION TRUE SYS TEST_PROG_SCHED_JOB_DEFINITION TRUE SYS TEST_PROG_JOB_DEFINITION TRUE SYS TEST_SCHED_JOB_DEFINITION TRUE 6 rows selected.
Jobs can be deleted using the DROP_JOB procedure:BEGIN -- Run job synchronously. DBMS_SCHEDULER.run_job (job_name => 'test_full_job_definition', use_current_session => FALSE); -- Stop jobs. DBMS_SCHEDULER.stop_job (job_name => 'test_full_job_definition, test_prog_sched_job_definition'); END; /
BEGIN DBMS_SCHEDULER.drop_job (job_name => 'test_full_job_definition'); DBMS_SCHEDULER.drop_job (job_name => 'test_prog_sched_job_definition'); DBMS_SCHEDULER.drop_job (job_name => 'test_prog_job_definition'); DBMS_SCHEDULER.drop_job (job_name => 'test_sched_job_definition'); END; / PL/SQL procedure successfully completed. -- Display job details. SELECT owner, job_name, enabled FROM dba_scheduler_jobs; OWNER JOB_NAME ENABL ------------------------------ ------------------------------ ----- SYS PURGE_LOG TRUE SYS GATHER_STATS_JOB TRUE 2 rows selected.
Job Classes
Job classes allow grouping of jobs with similar characteristics and resource requierments which eases administration. If the JOB_CLASS parameter of the CREATE_JOB procedure is undefined the job is assigned to the DEFAULT_JOB_CLASS. A job class is created using the CREATE_JOB_CLASS procedure:Jobs can be assigned to a job class either during or after creation using the SET_ATTRIBUTE procedure:-- Display the current resource consumer groups. SELECT consumer_group FROM dba_rsrc_consumer_groups; CONSUMER_GROUP ------------------------------ OTHER_GROUPS DEFAULT_CONSUMER_GROUP SYS_GROUP LOW_GROUP AUTO_TASK_CONSUMER_GROUP 5 rows selected. -- Create a job class. BEGIN DBMS_SCHEDULER.create_job_class ( job_class_name => 'test_job_class', resource_consumer_group => 'low_group'); END; / PL/SQL procedure successfully completed. -- Display job class details. SELECT job_class_name, resource_consumer_group FROM dba_scheduler_job_classes; JOB_CLASS_NAME RESOURCE_CONSUMER_GROUP ------------------------------ ------------------------------ DEFAULT_JOB_CLASS AUTO_TASKS_JOB_CLASS AUTO_TASK_CONSUMER_GROUP TEST_JOB_CLASS LOW_GROUP 3 rows selected.
Job classes can be dropped using DROP_JOB_CLASS procedure:BEGIN -- Job defined by an existing program and schedule and assigned toa job class. DBMS_SCHEDULER.create_job ( job_name => 'test_prog_sched_class_job_def', program_name => 'test_plsql_block_prog', schedule_name => 'test_hourly_schedule', job_class => 'test_job_class', enabled => TRUE, comments => 'Job defined by an existing program and schedule and assigned toa job class.'); DBMS_SCHEDULER.set_attribute ( name => 'test_prog_sched_job_definition', attribute => 'job_class', value => 'test_job_class'); END; / PL/SQL procedure successfully completed. -- Display job details. SELECT owner, job_name, job_class, enabled FROM dba_scheduler_jobs; OWNER JOB_NAME JOB_CLASS ENABL ------------------------------ ------------------------------ ------------------------------ ----- SYS PURGE_LOG DEFAULT_JOB_CLASS TRUE SYS GATHER_STATS_JOB AUTO_TASKS_JOB_CLASS TRUE SYS TEST_FULL_JOB_DEFINITION DEFAULT_JOB_CLASS TRUE SYS TEST_PROG_SCHED_JOB_DEFINITION TEST_JOB_CLASS TRUE SYS TEST_PROG_JOB_DEFINITION DEFAULT_JOB_CLASS TRUE SYS TEST_SCHED_JOB_DEFINITION DEFAULT_JOB_CLASS TRUE SYS TEST_PROG_SCHED_CLASS_JOB_DEF TEST_JOB_CLASS TRUE 7 rows selected.
The force option disables any dependent jobs and sets their job class to the default value. If the job class has no dependents the force option is not necessary.BEGIN DBMS_SCHEDULER.drop_job_class ( job_class_name => 'test_job_class', force => TRUE); END; / PL/SQL procedure successfully completed. -- Display job class details. SELECT job_class_name, resource_consumer_group FROM dba_scheduler_job_classes; JOB_CLASS_NAME RESOURCE_CONSUMER_GROUP ------------------------------ ------------------------------ DEFAULT_JOB_CLASS AUTO_TASKS_JOB_CLASS AUTO_TASK_CONSUMER_GROUP 2 rows selected.
Windows
Windows provide the link between the scheduler and the resource manager, allowing different resource plans to be activated at different times. Since job classes point to resource consumer groups, and therefore resource plans, this mechanism allows control over the resources allocated to job classes and their jobs during specific time periods.Only one window can be active (open) at any time, with one resource plan assigned to the window. The affect of resource plan switches is instantly visible to running jobs which are assigned to job classes. The interaction between the resource manager and the scheduler is beyond the scope of this document.
A window can be created using the CREATE_WINDOW procedure with a predefined or an inline schedule:
Windows can be opened and closed manually using the OPEN_WINDOW and CLOSE_WINDOW procedures:BEGIN -- Window with a predefined schedule. DBMS_SCHEDULER.create_window ( window_name => 'test_window_1', resource_plan => NULL, schedule_name => 'test_hourly_schedule', duration => INTERVAL '60' MINUTE, window_priority => 'LOW', comments => 'Window with a predefined schedule.'); -- Window with an inline schedule. DBMS_SCHEDULER.create_window ( window_name => 'test_window_2', resource_plan => NULL, start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, duration => INTERVAL '60' MINUTE, window_priority => 'LOW', comments => 'Window with an inline schedule.'); END; / PL/SQL procedure successfully completed. -- Display window group details. SELECT window_name, resource_plan, enabled, active FROM dba_scheduler_windows; WINDOW_NAME RESOURCE_PLAN ENABL ACTIV ------------------------------ ------------------------------ ----- ----- WEEKNIGHT_WINDOW TRUE FALSE WEEKEND_WINDOW TRUE FALSE TEST_WINDOW_1 TRUE FALSE TEST_WINDOW_2 TRUE FALSE 4 rows selected.
Windows can be dropped using the DROP_WINDOW procedure:BEGIN -- Open window. DBMS_SCHEDULER.open_window ( window_name => 'test_window_2', duration => INTERVAL '1' MINUTE, force => TRUE); END; / PL/SQL procedure successfully completed. -- Display window group details. SELECT window_name, resource_plan, enabled, active FROM dba_scheduler_windows; WINDOW_NAME RESOURCE_PLAN ENABL ACTIV ------------------------------ ------------------------------ ----- ----- WEEKNIGHT_WINDOW TRUE FALSE WEEKEND_WINDOW TRUE FALSE TEST_WINDOW_1 TRUE FALSE TEST_WINDOW_2 TRUE TRUE 4 rows selected. BEGIN -- Close window. DBMS_SCHEDULER.close_window ( window_name => 'test_window_2'); END; / PL/SQL procedure successfully completed. -- Display window group details. SELECT window_name, resource_plan, enabled, active FROM dba_scheduler_windows; WINDOW_NAME RESOURCE_PLAN ENABL ACTIV ------------------------------ ------------------------------ ----- ----- WEEKNIGHT_WINDOW TRUE FALSE WEEKEND_WINDOW TRUE FALSE TEST_WINDOW_1 TRUE FALSE TEST_WINDOW_2 TRUE FALSE 4 rows selected.
BEGIN DBMS_SCHEDULER.drop_window ( window_name => 'test_window_1', force => TRUE); DBMS_SCHEDULER.drop_window ( window_name => 'test_window_2', force => TRUE); END; / PL/SQL procedure successfully completed. -- Display window group details. SELECT window_name, resource_plan, enabled, active FROM dba_scheduler_windows; WINDOW_NAME RESOURCE_PLAN ENABL ACTIV ------------------------------ ------------------------------ ----- ----- WEEKNIGHT_WINDOW TRUE FALSE WEEKEND_WINDOW TRUE FALSE 2 rows selected.
Windows Groups
A window group is a collection of related windows. It can be created with 0, 1 or many windows as group members using the CREATE_WINDOW_GROUP procedure:Windows can be added and removed from a group using the ADD_WINDOW_GROUP_MEMBER and REMOVE_WINDOW_GROUP_MEMBER procedures:BEGIN DBMS_SCHEDULER.create_window_group ( group_name => 'test_window_group', window_list => 'test_window_1, test_window_2', comments => 'A test window group'); END; / PL/SQL procedure successfully completed. -- Display window group details. SELECT window_group_name, enabled, number_of_windowS FROM dba_scheduler_window_groups; WINDOW_GROUP_NAME ENABL NUMBER_OF_WINDOWS ------------------------------ ----- ----------------- MAINTENANCE_WINDOW_GROUP TRUE 2 TEST_WINDOW_GROUP TRUE 2 2 rows selected.
Window groups can be dropped using the drop_window_group procedure:BEGIN -- Create a new window. DBMS_SCHEDULER.create_window ( window_name => 'test_window_3', resource_plan => NULL, schedule_name => 'test_hourly_schedule', duration => INTERVAL '60' MINUTE, window_priority => 'LOW', comments => 'Window with a predefined schedule.'); DBMS_SCHEDULER.add_window_group_member ( group_name => 'test_window_group', window_list => 'test_window_3'); END; / PL/SQL procedure successfully completed. -- Display window group members. SELECT window_group_name, window_name FROM dba_scheduler_wingroup_members; WINDOW_GROUP_NAME WINDOW_NAME ------------------------------ ------------------------------ MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW TEST_WINDOW_GROUP TEST_WINDOW_1 TEST_WINDOW_GROUP TEST_WINDOW_2 TEST_WINDOW_GROUP TEST_WINDOW_3 5 rows selected. BEGIN DBMS_SCHEDULER.remove_window_group_member ( group_name => 'test_window_group', window_list => 'test_window_3'); END; / PL/SQL procedure successfully completed. -- Display window group members. SELECT window_group_name, window_name FROM dba_scheduler_wingroup_members; WINDOW_GROUP_NAME WINDOW_NAME ------------------------------ ------------------------------ MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW TEST_WINDOW_GROUP TEST_WINDOW_1 TEST_WINDOW_GROUP TEST_WINDOW_2 4 rows selected.
The force option must be used if the window group currently has members.BEGIN DBMS_SCHEDULER.drop_window_group ( group_name => 'test_window_group', force => TRUE); END; / PL/SQL procedure successfully completed. -- Display window group details. SELECT window_group_name, enabled, number_of_windowS FROM dba_scheduler_window_groups; WINDOW_GROUP_NAME ENABL NUMBER_OF_WINDOWS ------------------------------ ----- ----------------- MAINTENANCE_WINDOW_GROUP TRUE 2 1 row selected.
Enable, Disable and Attributes
All applicable scheduler objects can be enabled and disabled using the overloaded ENABLE and DISABLE procedures:The values for individual attributes of all scheduler objects can be altered using one of the SET_ATTRIBUTE overloads:BEGIN -- Enable programs and jobs. DBMS_SCHEDULER.enable (name => 'test_stored_procedure_prog'); DBMS_SCHEDULER.enable (name => 'test_full_job_definition'); -- Disable programs and jobs. DBMS_SCHEDULER.disable (name => 'test_stored_procedure_prog'); DBMS_SCHEDULER.disable (name => 'test_full_job_definition'); END; /
The values can be set to NULL using the SET_ATTRIBUTE_NULL procedure:BEGIN DBMS_SCHEDULER.set_attribute ( name => 'hourly_schedule', attribute => 'repeat_interval', value => 'freq=hourly; byminute=30'); END; /
For more information see:BEGIN DBMS_SCHEDULER.set_attribute_null ( name => 'hourly_schedule', attribute => 'repeat_interval'); END; /
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/756652/viewspace-242355/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle Database Scheduler整理OracleDatabase
- Oracle 10g Scheduler 特性Oracle 10g
- Oracle 10g Scheduler 全面介紹Oracle 10g
- oracle 10g flashback databaseOracle 10gDatabase
- 10G SCHEDULER 特性
- Rename Tablespace in Oracle database 10gOracleDatabase
- Tablespace Management Enhancements in Oracle Database 10gOracleDatabase
- Oracle 10g新增DROP DATABASE命令Oracle 10gDatabase
- SQL*Plus Enhancements in Oracle Database 10g(一)SQLOracleDatabase
- Oracle Database 10g Enhanced wait modelOracleDatabaseAI
- oracle 10g physical standby database creationOracle 10gDatabase
- Oracle 10g Limits - Logical Database LimitsOracle 10gMITDatabase
- oracle實驗記錄 (oracle 10G rman transport database)OracleDatabase
- Oracle Database 10g新特性-閃回表OracleDatabase
- DBMS_FILE_TRANSFER Package in Oracle Database 10gPackageOracleDatabase
- Flashback New Features and Enhancements in Oracle Database 10gOracleDatabase
- 【OCM】Oracle Database 10g: Performance Tuning(一)OracleDatabaseORM
- 【OCM】Oracle Database 10g: Performance Tuning(二)OracleDatabaseORM
- 【OCM】Oracle Database 10g: Performance Tuning(三)OracleDatabaseORM
- 【OCM】Oracle Database 10g: Performance Tuning(四)OracleDatabaseORM
- Oracle 10g FLASHBACK DATABASE 實驗記錄Oracle 10gDatabase
- Oracle Scheduler概述Oracle
- Oracle Database 10g新特性-回滾監視OracleDatabase
- 安裝oracle 10g EM database controlOracle 10gDatabase
- 《Oracle Database 10g安全性高效設計》OracleDatabase
- 【OCM】Oracle Database 10g: RAC for Administrators(一)OracleDatabase
- 【OCM】Oracle Database 10g: RAC for Administrators(二)OracleDatabase
- 【OCM】Oracle Database 10g: RAC for Administrators(三)OracleDatabase
- 【OCM】Oracle Database 10g: RAC for Administrators(四)OracleDatabase
- 【OCM】Oracle Database 10g: RAC for Administrators(五)OracleDatabase
- Transparent Data Encryption (TDE) in Oracle 10g Database Release 2Oracle 10gDatabase
- oracle scheduler任務Oracle
- Oracle DBMS_SCHEDULEROracle
- Oracle Database 10g新特性-閃回版本查詢OracleDatabase
- 使用 Oracle Database 10g中的閃回表特性OracleDatabase
- Upgrading from Oracle Database 10g to 11gOracleDatabase
- Oracle Database 10g新特性-改善的表空間管理OracleDatabase
- workaround with install oracle database 10G on redhat ES5OracleDatabaseRedhat