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