DBMS_SCHEDULER examples

May be interesting to some of you which haven't had the time testing and working with the DBMS_SCHEDULER. I collected some examples to show what is possible with that amazing tool.

DBMS_SCHEDULER is an internal Oracle package (since Version 10g) which provides database driven jobs.
It's divided into 3 parts:

• Time schedule part - dbms_scheduler.create_schedule
• Program declaration part - dbms_scheduler.create_program
  
• Job (conflation) part -dbms_scheduler.create_job

Examples of the dbms_scheduler.create_schedule part:

begin
-- daily from Monday to Sunday at 22:00 (10:00 p.m.)
dbms_scheduler.create_schedule
(schedule_name => 'INTERVAL_DAILY_2200',
 start_date=> trunc(sysdate)+18/24, -- start today 18:00 (06:00 p.m.)
 repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',
 comments=>'Runtime: Every day (Mon-Sun) at 22:00 o'clock');

-- run every 5 minute, every day
dbms_scheduler.create_schedule(
schedule_name  => 'INTERVAL_EVERY_5_MINUTES',
  start_date    => trunc(sysdate)+18/24,
  repeat_interval => 'freq=MINUTELY;interval=5',
  comments     => 'Runtime: Every day all 5 minutes');

-- run every minute, every day
dbms_scheduler.create_schedule(
schedule_name  => 'INTERVAL_EVERY_MINUTE',
  start_date    => trunc(sysdate)+18/24,
  repeat_interval => 'freq=MINUTELY;interval=1',
  comments     => 'Runtime: Every day every minute');

-- run every Sunday at 18:00 (06:00 p.m.)
dbms_scheduler.create_schedule
(schedule_name => 'INTERVAL_EVERY_SUN_1800',
 start_date=> trunc(sysdate)+18/24,
 repeat_interval=> 'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;',
 comments=>'Runtime: Run at 6pm every Sunday');
end;

Example of the dbms_scheduler.create_program part:

begin
-- Call a procedure of a database package
dbms_scheduler.create_program
(program_name=> 'PROG_COLLECT_SESS_DATA',
 program_type=> 'STORED_PROCEDURE',
 program_action=> 'pkg_collect_data.prc_session_data',
 enabled=>true,
 comments=>'Procedure to collect session information'
 );
end;

Example of the dbms_scheduler.create_job part:

begin
-- Connect both dbms_scheduler parts by creating the final job
dbms_scheduler.create_job
 (job_name => 'JOB_COLLECT_SESS_DATA',
  program_name=> 'PROG_COLLECT_SESS_DATA',
  schedule_name=>'INTERVAL_EVERY_5_MINUTES',
  enabled=>true,
  auto_drop=>false,
  comments=>'Job to collect data about session values every 5 minutes');
end;

Examples to change dbms_scheduler settings:

begin
-- change start time
DBMS_SCHEDULER.SET_ATTRIBUTE(
   name => 'INTERVAL_EVERY_5_MINUTES',
   attribute => 'start_date',
   value => to_date('22.06.2009 12:15','dd.mm.yyyy hh24:mi')
);

-- change repeat interval
DBMS_SCHEDULER.SET_ATTRIBUTE(
   name => 'INTERVAL_EVERY_MINUTE',
   attribute => 'repeat_interval',
   value => 'freq=MINUTELY;interval=2'
); 
end;

Example to run job immediate:

begin
dbms_scheduler.run_job('JOB_COLLECT_SESS_DATA',TRUE);
end;

Select job status:

-- All jobs
select * from user_scheduler_jobs;

-- Get information to my job
select * from user_scheduler_job_log where job_name='JOB_COLLECT_SESS_DATA';

Further information about the DBMS_SCHEDULER:
http://psoug.org/reference/OLD/dbms_scheduler.html
Oracle documentation about the DBMS_SCHEDULER

These scripts are tested in an Oracle XE environment (10.2.0.1).
I will extend this post whenever I need new scripts.