In the old days we used DBMS_JOB to schedule jobs in the database.
For more complex scheduling a program like Redwood Scheduler was used.
In Oracle 10g Oracle introduced DBMS_SCHEDULER.
The DBMS_JOB package is now depricated and in Oracle 10g it’s only
provided for backward compatibility. From Oracle 10g the DBMS_JOB package should not
be used any more, because is could not exist in a future version of Oracle.
With DBMS_SCHEDULER Oracle procedures and functions can be executed.
Also binary and shell-scripts can be scheduled.
The new scheduler has 3 main components:
– A schedule
– A program
– A job
If you have DBA rights you can do all the scheduling.
For administering job scheduling you need the priviliges belonging to
the SCHEDULER_ADMIN role.
To create and run jobs in your own schedule you need the ‘CREATE JOB’
With DBMS_JOB you needed to set an initialization parameter to start a
background process. With Oracle 10g DBMS_SCHEDULER this is not needed
If you want to user resource plans and/or consumer groups you need to
set a system parameter:
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
Getting started quickly
To quickly get a job running, you can use code like this:
begin dbms_scheduler.create_job( job_name => 'DEMO_JOB_SCHEDULE' ,job_type => 'PLSQL_BLOCK' ,job_action => 'begin package.procedure(''param_value''); end; ' ,start_date => '01/01/2006 02:00 AM' ,repeat_interval => 'FREQ=DAILY' ,enabled => TRUE ,comments => 'Demo for job schedule.'); end; /
This schedules a pl/sql block to be executed daily starting 1/1/2006
You can schedule things like this, but DBMS_SCHEDULER can reuse
You can build a schedule using components like program, schedule, job,
and window. We will now discuss these components in detail.
The program component is a collection of metadata about what will be run by the scheduler.
This includes information such as the program name, the type of program, and information about
arguments passed to the program.
begin dbms_scheduler.create_program ( program_name => 'DEMO_JOB_SCHEDULE' ,program_type => 'STORED_PROCEDURE' ,program_action => 'package.procedure' ,number_of_arguments => 1 ,enabled => FALSE ,comments => 'Demo for job schedule.'); dbms_scheduler.define_program_argument ( program_name => 'DEMO_JOB_SCHEDULE' ,argument_position => 1 ,argument_name => 'kol1' ,argument_type => 'VARCHAR2' ,default_value => 'default' ); dbms_scheduler.enable(name => 'DEMO_JOB_SCHEDULE'); end; /
The parameter program_type can have one of the following values:
dbms_scheduler also allows to execute shell scripts (Windows: *.bat
A schedule defines the frequence and date/time when a job is executed.
Similar to programs, schedules are database entities and can be saved in the database. The same schedule can be used by multiple jobs.
begin dbms_scheduler.create_schedule( schedule_name => 'DEMO_SCHEDULE' , start_date => '01/01/2006 22:00:00' , repeat_interval => 'FREQ=WEEKLY' , comments => 'Weekly at 22:00'); END; / To drop the schedule: begin dbms_scheduler.drop_schedule( schedule_name => 'DEMO_SCHEDULE' , force => TRUE ); end; /
Calendar expresions can have one of these values:
A job specifies what needs to executed and when. This can be a pl/sql procedure, c program, shell script
or other program.
You can specify a program and a schedule as part of a job definition, or you can use an existing program or schedule.
A job can belong to only 1 job class.
begin dbms_scheduler.create_job( job_name => 'DEMO_JOB1' , program_name =>'DEMO_JOB_SCHEDULE' , schedule_name =>'DEMO_SCHEDULE' , enabled => FALSE , comments => 'Run demo program every week at 22:00'); dbms_scheduler.set_job_argument_value( job_name => 'DEMO_JOB1' , argument_position => 1 , argument_value => 'param1'); dbms_scheduler.enable('DEMO_JOB1'); commit; end; / Or start shell script begin dbms_scheduler.create_job ( job_name => 'RUN_SHELL1', schedule_name => 'DEMO_SCHEDULE', job_type => 'EXECUTABLE', job_action => '/home/test/run_script.sh', enabled => true, comments => 'Run shell-script' ); end; /
Jobs can be monitored using Oracle Enterprise Manager 10g.
It’s also possible to use a number of views that have been created in
We will discuss some of these views here.
To show details on job run: select log_date , job_name , status , req_start_date , actual_start_date , run_duration from dba_scheduler_job_run_details
To show running jobs: select job_name , session_id , running_instance , elapsed_time , cpu_used from dba_scheduler_running_jobs;
To show job history: select log_date , job_name , status from dba_scheduler_job_log; show all schedules: select schedule_name, schedule_type, start_date, repeat_interval from dba_scheduler_schedules; show all jobs and their attributes: select * from dba_scheduler_jobs show all program-objects and their attributes select * from dba_scheduler_programs; show all program-arguments: select * from dba_scheduler_program_args;