Job scheduling from Oracle 10g with dbms_scheduler

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

Rights

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

With DBMS_JOB you needed to set an initialization parameter to start a
job coordinator
background process. With Oracle 10g DBMS_SCHEDULER this is not needed
any more.

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
02:00 AM.

You can schedule things like this, but DBMS_SCHEDULER can reuse
components.

You can build a schedule using components like program, schedule, job,
job class
and window. We will now discuss these components in detail.

Program

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.

Code example

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:
‘PLSQL_BLOCK’, ‘STORED_PROCEDURE’,’EXECUTABLE’.

dbms_scheduler also allows to execute shell scripts (Windows: *.bat
files) and
executables.

Schedule

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

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:
‘Yearly’,’Monthly’,’Weekly’,’Daily’,’Hourly’,’Minutely’,’Secondely’

 

Job

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.

Code example

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

Monitoring job-scheduling

Jobs can be monitored using Oracle Enterprise Manager 10g.
It’s also possible to use a number of views that have been created in
Oracle 10g.
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;

Š

Leave a Reply

Your email address will not be published. Required fields are marked *