DBMS_SCHEDULER as a new alternative for DBMS_JOB

Patrick Sinke

Many people have a dislike of Dbms_job. Setting the right parameters
requires good knowledge of the input parameters and you probably need a
calculator too! In Oracle 10g, a new package might make things easier. 

What’s in a name

If you check 10g’s data dictionary, you’ll find a new package by the name dbms_scheduler.
One of the most obvious improvements is the naming convention of the
functions in this package. DBMS_JOB.Submit is now named
DBMS_SCHEDULER.Create_job and DBMS_JOB.Run goes by the name
DBMS_SCHEDULER.Run_job. You may disagree, but I think the new names
make a lot more sense.

In control of intervals

Fortunately,
Oracle made major improvements in the most annoying flaw of DBMS_JOB:
defining the interval. For instance, if you want a job to start every
morning at 06:00 exactly, the parameter “interval” usually contains
something like ‘TRUNC(SYSDATE) + 1 + 21/24’. With DBMS_SCHEDULER, the new parameter “repeat_interval” is set to ‘FREQ=DAILY’. In combination with a start_date = ’20-APR-2006 06:00′ the job starts at 06:00. Everyday at
06:00. And not at 06:10:14 because the previous run lasted 10 minutes
14 seconds and you entered an interval of “SYSDATE + 1”.

Scheduler and program objects

Another notable thing is the option to create scheduler and program objects. This is quite interesting too, because now you can define a custom schedule which you can reuse in all jobs.

This example creates a schedule which runs every hour on the half hour.

BEGIN
  DBMS_SCHEDULER.create_schedule
  ( schedule_name   => 'run_every_hour'
  , start_date      => SYSTIMESTAMP
  , repeat_interval => 'freq=hourly; byminute=30'
  , end_date        => NULL
  , comments        => 'This schedule starts every hour at the half hour'
  ) ;
END;
/

Next step is creating a program object with parameters:

BEGIN
  dbms_scheduler.create_program
    ( program_name        => 'Exampleprogram'
    , program_type        => 'STORED_PROCEDURE'
    , program_action      => 'package.procedure'
    , number_of_arguments => 1
    , enabled             => FALSE
    , comments            => 'A demo program object.' );
      
  dbms_scheduler.define_program_argument
    ( program_name      => 'Exampleprogram'
    , argument_position => 1
    , argument_name     => 'parameter_1'
    , argument_type => 'VARCHAR2'
    , default_value => 'Scott'
    ) ;
    dbms_scheduler.enable( name => 'Exampleprogram' );
END;
/

Creating a job is now easier than ever:

BEGIN 
  DBMS_SCHEDULER.create_job
  ( job_name      => 'Examplejob'
  , program_name  => 'Exampleprogram'
  , schedule_name => 'run_every_hour'
  , enabled       => FALSE
  , comments      => 'Job that starts according to predefined schedule'
  ) ;
  dbms_scheduler.set_job_argument_value
  ( job_name => 'Examplejob'
  , argument_position => 1
  , argument_value => 'Tiger' );

  dbms_scheduler.enable( 'Examplejob' );

  commit;

END;
/
The parameter program_type can be one of the values:
‘PLSQL_BLOCK’, ‘STORED_PROCEDURE’ or ‘EXECUTABLE’.  With ‘executable’, dbms_scheduler allows to execute shell scripts and
executables.

Ooops…

Please
note that in Oracle 10g R1 there is a vulnerability in which user with
access to DBMS_SCHEDULER and the create job privilege are able to gain
DBA and remote OS rights. Pete Finnigan found the bug and there is a
patch available as described here.

 

 

 

Next Post

Cost Based Optimizing through time travel? - the value of meta-data for enabling Query Rewrite

Facebook0TwitterLinkedinQuery Rewrite is an increasingly more important feature of the Oracle database. Linked intimately to Materialized Views, Query Rewrite is used by the CBO to make queries leverage intermediate results – often aggregate values or pre-joined records – available in Materialized Views when executing a query, thus preventing the need […]