Scheduling Jobs with DBMS_SCHEDULER
DBAs, for years, are writing OS level scripts to execute different database related tasks and schedule it via cront tab in Linux. The cron jobs work perfectly well until Oracle released DBMS_SCHEDULER in 10g version.
Note: DBMS_SCHEDULER has introduced many benefits yet, many DBAs still stick to OS level scripting.
Before you learn how to schedule jobs via DBMS_SCHEDULER, let us compare it with cron jobs (OS level scripts)
DBMS_SCHEDULER syntax works same regardless of OS
Can run jobs based on database events
If DB is down during a job schedule, it will be executed again once DB starts up
You can run jobs on remote machines (11gR1 and above)
Schedule chain of jobs one after another
All in all, it's time for you to migrate most of your OS-level scripts to DBMS_SCHEDULER!
How DBMS_SCHEDULER works?
Let us first get familiar with different components inside DBMS_SCHEDULER. There many components but we will be looking at the most important ones.
As you can see from the above diagram, we need to
First define a program that is capable of executing PL/SQL script, shell script or stored procedures
Next, we need to define a schedule for the above program. The schedule contains execution frequency
Finally, we need to create a job with program name (created in first step) and schedule (created in second step) to DBMS_SCHEDULER
Note: you can directly write a job which includes a program and a schedule. In that case you need not specifically create a program or schedule. But, it is always good to follow standards. Always create a program, followed by schedule and finally job!
Ultimately you will be working with three most important procedures under DBMS_SCHEDULER
STEP 1 – Create program
A program tells DBMS_SCHEDULER as to what to execute. It is capable of executing
OS level executable file
Sample program to execute a PL/SQL program:
BEGIN DBMS_SCHEDULER.create_program( program_name => 'plsql_program', program_type => 'PLSQL_BLOCK', program_action => 'BEGIN DBMS_STATS.gather_schema_stats(''HR''); END;', enabled => TRUE, comments => 'Program to gather HR user statistics'); DBMS_SCHEDULER.enable (name=>'plsql_program'); END; /
Sample program to execute a stored procedure
Note: you must define number_of_arguments even before you can enable a program. Notice arguments in the below code must be defined before enabling the program
BEGIN DBMS_SCHEDULER.create_program( program_name => 'stored_procedure_program', program_type => 'STORED_PROCEDURE', program_action => 'DBMS_STATS.gather_schema_stats', number_of_arguments => 1, enabled => FALSE, comments => 'Program to gather HR stats using stored procedure'); DBMS_SCHEDULER.define_program_argument( program_name => 'stored_procedure_program', argument_name => 'ownname', argument_position => 1, argument_type => 'VARCHAR2', default_value => 'SYS'); DBMS_SCHEDULER.enable (name=>'stored_procedure_program'); END; /
Sample program to execute an OS level executable script file:
BEGIN DBMS_SCHEDULER.create_program( program_name => 'executable_program', program_type => 'EXECUTABLE', program_action => '/u02/rman/scripts/db_full_bkp.sh', enabled => TRUE, comments => 'Program to trigger rman full backup'); DBMS_SCHEDULER.enable (name=>'executable_program'); END; /
Drop, enable, disable program
To drop a program
BEGIN DBMS_SCHEDULER.drop_program(program_name=>'plsql_program'); END; /
To enable / disable a program
-- to disable a program BEGIN DBMS_SCHEDULER.disable (name=>'plsql_program'); END; / -- to enable a program BEGIN DBMS_SCHEDULER.enable (name=>'plsql_program'); END; /
View program details
You must query DBA_SCHEDULER_PROGRAMS to view details about scheduled programs
set lines 999; col owner for a20; col program_name for a30; SELECT owner, program_name, enabled FROM dba_scheduler_programs where program_name like '&enter_program_name';
STEP 2 – Create schedule
Schedules is what defines DBMS_SCHEDULER when to run a program and at what frequency / interval.
Below is a sample schedule that repeats every hour, exactly at 00 minutes and has no end date
BEGIN DBMS_SCHEDULER.create_schedule ( schedule_name => 'hourly_sched', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, comments => 'Run every hour at 00 minutes everyday'); END; /
Drop a schedule
Use below code to drop an existing schedule
BEGIN DBMS_SCHEDULER.drop_schedule (schedule_name => 'hourly_sched'); END; /
View schedule details
To see schedule details, you must query DBA_SCHEDULER_SCHEDULES view
set lines 999; col schedule_name for a30; SELECT owner, schedule_name from DBA_SCHEDULER_SCHEDULES;
More schedule examples
run everyday at midnight
'freq=daily; byhour=0; byminute=0; bysecond=0;'
run everyday at 4 pm
'freq=daily; byhour=16; byminute=0; bysecond=0;'
run every hour at 10 minutes. 1:10, 2:10 …..
'freq=hourly; byminute=10; bysecond=0;'
run every 5 minutes
'freq=minutely; interval=5; bysecond=0;'
run every monday and thursday at 9 pm
'freq=weekly, byday=mon,thu; byhour=21; byminute=0; bysecond=0;'
run friday of each quarter
'freq=monthly; bymonth=1,4,7,10; byday=fri;'
STEP 3 – Create job
As mentioned earlier, you need not create program and schedule separately. You can define both program and schedule inside a job. But, it is always good to follow a standard.
DBMS_SCHEDULER is all about jobs. A job consists of a program and a schedule.
DBMS_SCHEDULER cannot execute a program or schedule independently. You must create a job and submit to the scheduler.
BEGIN DBMS_SCHEDULER.create_job ( job_name => 'test_sched_job', program_name => 'plsql_program', schedule_name => 'hourly_sched', enabled => TRUE, comments => 'My test scheduler job'); END; /
Drop, enable, disable job
To drop a job
BEGIN DBMS_SCHEDULER.drop_job (job_name=>'test_sched_job'); END; /
To enable / disable a job
-- to disable a job BEGIN DBMS_SCHEDULER.disable (name=>'test_sched_job'); END; / -- to enable a job BEGIN DBMS_SCHEDULER.enable (name=>'test_sched_job'); END; /
View job details
To see job details, you must query DBA_SCHEDULER_JOBS view
set lines 999; col job_name for a30; select owner, job_name, enabled from dba_scheduler_jobs;
Run jobs manually
Even though you have scheduled jobs inside DBMS_SCHEDULER on a set schedule, you can manually execute jobs too
BEGIN DBMS_SCHEDULER.run_job (job_name => 'test_sched_job', use_current_session => TRUE); END; /
View job status
To see status of the scheduler job executed previously, you must query DBA_SCHEDULER_JOB_RUN_DETAILS view
select job_name, status, run_duration from dba_scheduler_job_run_details where job_name='&enter_job_name';
Find the details of the program attached to a job
SELECT job_name, enabled, program_name FROM dba_scheduler_jobs WHERE job_name LIKE 'TEST%';
Find schedule details attached to a job
SELECT job_name, schedule_name, start_date FROM dba_scheduler_jobs WHERE job_name like 'TEST%';
Find job current status if it is running or not
SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME LIKE 'TEST%';
Below are different job states
Disabled – Job is disabled
Scheduled – Job is scheduled to be executed
Running – Job is currently running
Completed – Job completed, not scheduled to run again
Stopped – Job scheduled to run once and was stopped during its run
Broken – Job is broken and has issues
Failed – Job scheduled to run once and failed
Succeeded – Job scheduled to run once and completed successfully
Check progress of all running jobs
SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;
Find the log details of job runs
SELECT to_char(log_date, 'DD-MON-YY HH24:MM:SS') TIMESTAMP, job_name, status, SUBSTR(additional_info, 1, 40) ADDITIONAL_INFO FROM user_scheduler_job_run_details WHERE job_name like 'TEST%' ORDER BY log_date ;