Many DBAs find Oracle's job scheduling mechanism hopelessly clunky. (Personally, I don't -- I find it easy to use and infinitely flexible, but you do have to put in a bit of work to harness that flexibility.) In response, Oracle has provided a whole new way to schedule jobs in 10g databases: dbms_scheduler.
There are two key things to note about dbms_scheduler, which is a PL/SQL supplied package like dbms_job. The first is that it lets you separate out schedules and programs from jobs. So, you can create a schedule, eg RUN_EVERY_NIGHT, and use it for several jobs; or you can create a program object, eg. one using an anonymous PL/SQL block to analyze some tables, independently of any schedule. A job can combine a named schedule and a named program. (You can also submit jobs with inline schedules and inline programs.)
The second thing to note is that it uses Oracle's new calendaring syntax. Instead of messing around with date arithmetic, you can now specify daily, weekly, monthly, hourly schedules, or a combination of the above.
In this example, first we create some schedules. There are three things to note here:
begin dbms_scheduler.create_schedule (schedule_name => 'Mon_To_Sat_1800', start_date=> trunc(sysdate)+18/24, repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT; BYHOUR=18;', comments=>'Run at 6pm all days but Sunday'); dbms_scheduler.create_schedule (schedule_name => 'Sun_1800', start_date=> trunc(sysdate)+18/24, repeat_interval=> 'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;', comments=>'Run at 6pm Sunday'); end;
Note the use of calendaring syntax in the repeat_interval parameter. You can find more examples of calendaring syntax in the 10g Administrator's Guide here. It is fully documented in the PL/SQL Supplied Packages documentation here.
The create_schedule procedure also provides a very powerful mechanism to let you control your schedules: the INCLUDE, EXCLUDE and INTERSECT clauses you can supply to repeat_interval. Basically, you can include one schedule in another, exclude it, or make a schedule that is just the intersection of two schedules. For example, you could create a HOLIDAYS schedule listing all your office holidays, and then use it to create another schedule which used the EXCLUDE=HOLIDAYS; to run every day except HOLIDAYS. Read more details in the dbms_scheduler documentation here.
The next step is to create some program objects.
begin dbms_scheduler.create_program (program_name=> 'INCREMENTAL_WIDGET', program_type=> 'STORED_PROCEDURE', program_action=> 'run_widget.run_incremental', enabled=>true, comments=>'Run my widget on today''s new rows' ); dbms_scheduler.create_program (program_name=> 'FULL_WIDGET', program_type=> 'STORED_PROCEDURE', program_action=> 'run_widget.run_full', enabled=>true, comments=>'Run my widget on all rows' ); end;
Notes:
Full documentation on the create_program procedure can be found here.
The last step is to create a job object, which brings together a schedule and a program.
begin dbms_scheduler.create_job (job_name => 'RUN_INCREMENTAL_WIDGET', program_name=> 'INCREMENTAL_WIDGET', schedule_name=>'SYS.MON_TO_SAT_1800', enabled=>true, auto_drop=>false, comments=>'Run widget incrementally every day but Sunday at 6 pm'); dbms_scheduler.create_job (job_name => 'RUN_FULL_WIDGET', program_name=> 'FULL_WIDGET', schedule_name=>'SYS.SUN_1800', enabled=>true, auto_drop=>false, comments=>'Run a full widget every Sunday at 6 pm'); end;
Again, note that the default for the enabled parameter is false. The auto_drop parameter defaults to true and means the job is automatically dropped after it's executed (for one-time jobs) or disabled.
You can get much fancier with create_job: you can create job classes, assign the job a priority, make it kick off based on an AQ event, give these AQ events a named queue, make it expire on a given end_date, set a max number of runs, or automatically generate the job name. See the documentation.