Introduction and Scope:

The intention of this post is to give you a very basic information about Oracle scheduler utility in Oracle 10g database.

The Oracle Database 10g Scheduler feature offers the best of all worlds: a job scheduler utility right inside the database that is sufficiently powerful to handle all types of jobs, not just PL/SQL code segments, and that can help you create jobs either with or without associated programs and/or schedules. Best of all, it comes with the database at no additional cost. In this installment, we’ll take a look at how it works.

Three Concepts

Three concepts are involved in Scheduler basic functionality.

1. Job
2. Program
3. Schedule

Purpose of the Job is to execute any program or executable provided in the Job details as per the schedule provided. The example is as given below.

We can specify the OS executable directly without creating it as a program first.

Example we want to run a shell script /home/arup/dbtools/move_arcs.sh

The definition of job will look as given below.

Method I : Creating Job Directly

begin
dbms_scheduler.create_job
(
job_name => ‘ARC_MOVE_2’,
job_type => ‘EXECUTABLE’,
job_action => ‘/home/arup/dbtools/move_arcs.sh’,
repeat_interval => ‘FREQ=MINUTELY; INTERVAL=30’,
enabled => true,
comments => ‘Move Archived Logs to a Different Directory’
);
end;
/

Here we are creating Job directly and providing the script name to execute (job_action) , the job type (job_type) and the schedule (repeat_interval => ‘FREQ=MINUTELY; INTERVAL=30’) according to which it has to execute the job.

The above method is one of the way of creating the job. Remember that you have to create a job for your program to execute as per defined schedule.

Method II : Creating a program and attaching to a Job

Another method of creating a job is to first create a program and use that program while creating a job as given below.

Creating Program First

begin
dbms_scheduler.create_program
(
program_name => ‘MOVE_ARCS’,
program_type => ‘EXECUTABLE’,
program_action => ‘/home/arup/dbtools/move_arcs.sh’,
enabled => TRUE,
comments => ‘Moving Archived Logs to Staging Directory’
);
end;
/

Using that program while creating a job

begin
dbms_scheduler.create_job
(
job_name => ‘ARC_MOVE’,
program_name => ‘MOVE_ARCS’,
repeat_interval => ‘FREQ=MINUTELY; INTERVAL=30’,
comments => ‘Move Archived Logs to a Different Directory’,
enabled => TRUE
);
end;
/

So we just create a separate program and attach the same to the job. But if you note here that we have mentioned the schedule in the job itself. Obviously the third method will be to create a schedule separately and attach the same to the job.

Method III : Creating a program, schedule and attaching to a Job

Creating a program first.

begin
dbms_scheduler.create_program
(
program_name => ‘MOVE_ARCS’,
program_type => ‘EXECUTABLE’,
program_action => ‘/home/arup/dbtools/move_arcs.sh’,
enabled => TRUE,
comments => ‘Moving Archived Logs to Staging Directory’
);
end;
/

Creating schedule for the job to execute.

begin
dbms_scheduler.create_schedule
(
schedule_name => ‘EVERY_30_MINS’,
repeat_interval => ‘FREQ=MINUTELY; INTERVAL=30’,
comments => ‘Every 30-mins’
);
end;
/

Attaching program and schedule to the job.

begin
dbms_scheduler.create_job
(
job_name => ‘ARC_MOVE’,
program_name => ‘MOVE_ARCS’,
schedule_name => ‘EVERY_30_MINS’,
comments => ‘Move Archived Logs to a Different Directory’,
enabled => TRUE
);
end;
/

This is a more cleaner way of doing the things.

You can use any of the above methods to create your schedule in the database.

Checking the validity of Schedules

We can also check if our schedules are valid or not. Means we have a way to check what are the dates and times when our job is going to run in future. This is possible using a procedure called EVALUATE_CALENDER_STRING present in DBMS_SCHEDULER package.

Lets see the usage as given below.

declare
L_start_date TIMESTAMP;
l_next_date TIMESTAMP;
l_return_date TIMESTAMP;
begin
l_start_date := trunc(SYSTIMESTAMP);
l_return_date := l_start_date;
for ctr in 1..10 loop
dbms_scheduler.evaluate_calendar_string(
‘freq=hourly; byminute=30’,
l_start_date, l_return_date, l_next_date
);
dbms_output.put_line(‘Next Run on: ‘ ||
to_char(l_next_date,’mm/dd/yyyy hh24:mi:ss’)
);
l_return_date := l_next_date;
end loop;
end;
/

Next Run on: 04/03/2008 00:30:00
Next Run on: 04/03/2008 01:30:00
Next Run on: 04/03/2008 02:30:00
Next Run on: 04/03/2008 03:30:00
Next Run on: 04/03/2008 04:30:00
Next Run on: 04/03/2008 05:30:00
Next Run on: 04/03/2008 06:30:00
Next Run on: 04/03/2008 07:30:00
Next Run on: 04/03/2008 08:30:00
Next Run on: 04/03/2008 09:30:00
You can change the freq portion and check for its run frequency and then put the same in create_schedule procedure.

Changing Attributes values

You can change the value of any attribute using SET_ATTRIBUTE procedure of DBMS_SCHEDULER procedure as given below.

BEGIN
DBMS_SCHEDULER.set_attribute (
name => ‘ARCHIVE_CHEK_LIST_SCHEDULE’,
attribute => ‘repeat_interval’,
value => ‘freq=daily’);
END;
/

This will change our schedule to run daily once.

These are just the basic concepts for scheduling. I will be covering more detailed concepts like window, job classes, plans and priorities in my next post.

Monitoring Jobs

You can monitor the status of the jobs that was scheduled and ran previously and also the jobs which are currently running. Below are some of the important tables which you can use to monitor the jobs.

To get the details of job runs:

You can use DBA_SCHEDULER_JOB_RUN_DETAILS table.

To get the jobs which are currently running

You can use DBA_SCHEDULER_RUNNING_JOBS table.  This table will not give any rows if there are no jobs which are running at that instance.

To get job history of previous run jobs:

You can use DBA_SCHEDULAR_JOB_LOG table. The output is as shown below.

select log_date
,      job_name
,      status
from dba_scheduler_job_log
where job_name = ‘ARCHIVE_CHEK_LIST_JOB’

To get all the schedules in database – DBA_SCHEDULER_SCHEDULES

To get all the jobs and their attributes –  DBA_SCHEDULER_JOBS

Note that DBA_JOBS is used by DBMS_JOBS package, the old version for scheduling jobs.

To get all the information about programs  – DBA_SCHEDULER_PROGRAMS

To get all program arguements  – DBA_SCHEDULER_PROGRAM_ARGS

Hope this helps !!

References:

Oracle Database 10g: Top 20 DBA Features