Oracle Scheduler

Readers,

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.

Thanks,

Your Truly

Advertisements

About Badal Chowdhary

I am a Software Engineer by profession. I have done SCJP and SCWCD certifications. Like working on cutting edge technologies and frameworks. Driven by challenges and fascinated by technology. I love playing and watching sports: Cricket, Ping Pong, Tennis, Badminton, Racket Ball and Gym.
This entry was posted in Oracle and tagged , . Bookmark the permalink.

One Response to Oracle Scheduler

  1. Hiren Patel says:

    Gr8 post. I guess I should try removing CRON jobs which just executes Oracle Procedure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s