The new Oracle scheduler and how it works

Traditionally you used the dbms_job.submit to create a job (before Oracle 9i) , and you summited as parameters , the job number, what you want to be run, the start date and the interval, simple but if you ask me not much flexibility.

Typical example: exec dbms_job.submit(Jobno, 'begin (procedure_name); end; ' , SYSDATE, 'SYSDATE + 36/86400'); (Every 36 seconds)

In 10g and above Oracle provides greater flexibility and in my opinion clarity, it used to be a pain trying to come up with the right interval formulas for odds intervals. (for example 3/1440 would be execute every 3 minutes, but at simple view that is not that clear).

Now in 10g Oracle splits the process in 3 to provide flexibility, first you create a schedule, next you create a program and then you create a job and assign that schedule and program for the job. This allows you to reuse the schedule or the programs with another jobs.

NOTE: The examples below are for illustration purpose only and in most of the cases you need to complete and adapt the syntax to what you want to do.

Example of creating a schedule:

Begin
sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=WEEKLY;BDAY=WED;BYHOUR=8;BYMINUTE=0;BYSECOND=0',
start_date => to_timestamp_tz('2008-05-06 US/Eastern','YYYY-MM-DD TZR'),
comments => 'Wednesday AM Schedule',
schedule_name => '"RORTA"."WED_AM"');
end;
/

The schedule above is created on the rorta schema with the name WED_AM to be executed every Wednesday at 8 AM.

Creating a program:

You need first to create the program with the dbms_scheduler.create_program

begin
  dbms_scheduler.create_program (
      program_name => 'RORTA.WEEKLY_CHECK'
     ,program_type => 'STORED PROCEDURE'
     ,program_action => 'package.procedure'
     ,number_of_arguments => 1
     ,enabled => FALSE
     ,comments => 'comments');

You can pass arguments to your programs, see below

dbms_scheduler.define_program_argument (
      program_name => 'RORTA.WEEKLY_CHECK'
     ,argument_position => 1
     ,argument_name => 'kol1'
     ,argument_type => 'VARCHAR2'
     ,default_value => 'default'
   );

Now you enable the program

exec dbms_scheduler.enable(name => 'RORTA.WEEKLY_CHECK');

Creating the job:

A job is a combination of the schedule and a program. look at the example below:

begin
sys.dbms_scheduler.create_job(
job_name => 'RORTA.WEEKLY_CHECK',
program_name => 'RORTA.WEEKLY_CHECK',
schedule_name => 'RORTA.WED_AM'
comments => "Check the database Health",
auto_drop => FALSE,
enabled => TRUE);
end;
/

In addition your job can execute an external executable as follow:

begin
dbms_scheduler.create_job ( job_name => 'RUN_SHELL',
schedule_name => 'SHELL_SCHEDULE',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/script.sh',
enabled => true,
comments => 'Shell-script' );
end;
/

Use the following views and commands to monitor your jobs

- dba_scheduler_job_run_details
- dba_scheduler_running_jobs
- dba_scheduler_job_log
- show all schedules
- dba_scheduler_schedules
- dba_scheduler_jobs
- dba_scheduler_programs
- dba_scheduile_programs_args