DBMS_SCHEDULER examples
Von Tobias Arnhold →
6.25.2009
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:
Example of the dbms_scheduler.create_program part:
Example of the dbms_scheduler.create_job part:
Examples to change dbms_scheduler settings:
Example to run job immediate:
Example to restart job:
Select job status:
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.
Update 11.09.2010:
Run a job without program and schedule plan (all in one command):
@Sue
Dynamic Scheduled jobs from inside APEX are no problem. But be careful if you use "execute immediate"!
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
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 hour, every day
dbms_scheduler.create_schedule(
schedule_name => 'INTERVAL_EVERY_HOUR',
start_date => trunc(sysdate)+18/24,
repeat_interval => 'freq=HOURLY;interval=1',
comments => 'Runtime: Every day every hour');
-- 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;
Example to restart job:
begin
dbms_scheduler.disable('JOB_COLLECT_INST_INFO');
dbms_scheduler.enable('JOB_COLLECT_INST_INFO');
end;
Select job status:
-- All jobs
select * from user_scheduler_jobs;
-- Get information to job
select * from user_scheduler_job_log order by log_date desc;
-- Show details on job run
select * from user_scheduler_job_run_details;
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.
Update 11.09.2010:
Run a job without program and schedule plan (all in one command):
dbms_scheduler.create_job (
job_name => 'TEST_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'begin /* some process code */ commit; end;',
number_of_arguments => 0,
start_date => sysdate +1/24/59, -- sysdate + 1 minute
job_class => 'ADMIN', -- Priority Group
enabled => TRUE,
auto_drop => TRUE,
comments => 'Testrun');
@Sue
Dynamic Scheduled jobs from inside APEX are no problem. But be careful if you use "execute immediate"!
