Schedulers are always a yes yes for a Software Engineer. You can do scheduling in Java via Quartz, Unix via Cron, Windows via Windows Scheduler UI (not so reliable though). Oracle too allows to schedule objects via dbms_scheduler package.
Ok, so you have a written a fundo procedure and you are proud of it. BUT there is a twist. This proc should run every day at , say, 6 AM. No problem. You can go to office @ 6, run the proc, come back home and sleep. Problem solved!! 祝贺
Or you can let Oracle wake up at 6 and do the work. Lets get to the meat.
There are 3 steps:
1) Create Schedule
begin dbms_scheduler.create_schedule ( schedule_name => 'SCHED_NAME', start_date => trunc(sysdate)+20/24, repeat_interval => 'freq=DAILY; BYDAY=SAT; BYHOUR=06;', comments => 'my first schedule to run at 6 AM every Sat'); end;
repeat_interval can be modified as per your reqd schedule.
start_date is the time for first execution.
2) Create Program
begin dbms_scheduler.create_program ( program_name => 'PROG_NAME', program_type => 'STORED_PROCEDURE', program_action => 'PROC_NAME_TO_BE_EXECUTED', enabled => true, comments => 'Tell Oracle which proc to run' );
3) Create Job
begin DBMS_SCHEDULER.CREATE_JOB( job_name => 'JOB_NAME', program_name => PROG_NAME', schedule_name => 'SCHED_NAME', enabled => true, AUTO_DROP => FALSE, comments => 'Link your schedule and program to a job.' ); end;
Thats it! You have scheduled your procedure using Oracle’s Scheduler package.
All your job details (success/failure) can be tracked via dictionary tables. Examples below:
select * from user_scheduler_schedules; select * from USER_scheduler_programs; select * from user_scheduler_jobs; select * from user_scheduler_job_log order by log_date desc; select * from user_scheduler_job_run_details;
To drop an existing scheduled job, you have to first drop job and then schedule & program in any order.
begin dbms_scheduler.drop_job('JOB_NAME'); dbms_scheduler.drop_program('PROG_NAME'); dbms_scheduler.drop_schedule('SCHED_NAME'); end;
Hope this post was useful.