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


·         Schedules


·         Jobs


·         Job Classes


·         Windows


·         Windows Groups


·         Enable, Disable and Attributes


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:


-- 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.

Notice how programs that accept arguments must have the arguments defined before they can be enabled.

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:


-- 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.

Notice how the interval is defined using the calendaring syntax. A schedule can be dropped using the DROP_SCHEDULE procedure:


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:


-- 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 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:


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;
/

Jobs can be deleted using the DROP_JOB procedure:


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:


-- 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.

Jobs can be assigned to a job class either during or after creation using the SET_ATTRIBUTE 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.

Job classes can be dropped using DROP_JOB_CLASS procedure:


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.

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.


 


arrow
arrow
    全站熱搜
    創作者介紹
    創作者 噗噗噗的潛水珽 的頭像
    噗噗噗的潛水珽

    潛水珽的異想世界

    噗噗噗的潛水珽 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄