DBMS_SCHEDULER Package in Oracle Database

  Dbms_scheduler is a more sophisticated job scheduler introduced from Oracle 10g and this package provides a collection of scheduling functions and procedures that are callable from any PL/SQL program.  Here are some examples.

Creating Job 

BEGIN
Dbms_Scheduler.create_job(
job_name => 'XX_DAILY_HEALTH_MONITOR_JOB',         --Job Name
job_type => 'STORED_PROCEDURE',                              --Type
job_action => 'SYS.XX_DAILY_HEALTH_MONITOR',         --Object Name
start_date => SYSTIMESTAMP,                                      --Job Start Date
repeat_interval => 'freq=daily; byhour=8',                    --Frequency
enabled => TRUE,                                                        --Status
comments => 'Daily DB Health Satus');                          --Comment
END;
/


Creating a Job That Calls an Executable

begin
dbms_scheduler.create_job (
   job_name           =>  'XX_DAILY_HEALTH_MONITOR_JOB',
   job_type           =>  'executable',
   job_action         =>  '/home/oracle/bin/health.sh',
   start_date         =>  '01-sep-2014 06:00:00 am',
   repeat_interval    =>  'freq=daily',
   enabled            =>  true);
end;
/


Run Job Manually

begin
dbms_scheduler.run_job (job_name => 'XX_DAILY_HEALTH_MONITOR_JOB');
end;
/


Modified Attribute

You can change anything about a scheduled job, except its name, using set_attribute Procedure

begin
dbms_scheduler.set_attribute (
name               =>  'XX_DAILY_HEALTH_MONITOR_JOB',
attribute          =>  'repeat_interval',
value              =>  'freq=daily; byhour=3');
end;
/


Stop Running Job

begin
dbms_scheduler.stop_job (job_name => 'XX_DAILY_HEALTH_MONITOR_JOB');
end;
/


Disable Job

begin
dbms_scheduler.disable (job_name => 'XX_DAILY_HEALTH_MONITOR_JOB');
end;
/


Enable Job

begin
dbms_scheduler.enable (job_name => 'XX_DAILY_HEALTH_MONITOR_JOB');
end;
/


Stop Job Permanantly

begin
dbms_scheduler.drop_job ('XX_DAILY_HEALTH_MONITOR_JOB');
end;
/

Detail of some expression defined by repeat_interval attribute

repeat_interval	Description
freq=hourly	Run every hour
freq=daily; byhour=3	Run at 3 am every day
freq=daily; byhour=8,20	Run at 8 am and 8 pm every day
freq=monthly; bymonthday=1	Run on the first day of every month
freq=monthly; bymonthday=-1	Run on the last day of every month
freq=yearly; bymonth=sep; bymonthday=20;	Run yearly on September 20th

Some useful scheduler views

DBA_SCHEDULER_JOBS

DBA_SCHEDULER_PROGRAMS

DBA_SCHEDULER_RUNNING_JOBS