techblog 

In version 10g of the Oracle Database the Oracle Scheduler (dbms_scheduler package) was introduced. This allows the scheduling of jobs within an Oracle database and has many more features than the older dbms_job package, which is still available in Oracle 10g.

You can schedule jobs to run at a designated date and time or upon the occurrence of an event. You can set priorities for and dependencies between jobs.

We have previously used only the dbms_job package, but recently encountered a complicated scheduling requirement which it could not handle. We therefore decided to make the change to dbms_scheduler.

There is an overview of the scheduler here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2285

A guide to the differences between dbms_job and dbms_scheduler is here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/jobtosched.htm#i1018602

More detailed documents are here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schedover.htm#i1106753
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/scheduse.htm#i1033533
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schedadmin.htm#i1007589

The rest of this document contains examples of the use of views and procedures related to the scheduler.

Examine all Scheduled Jobs

select * from all_scheduler_jobs;

select * from all_scheduler_job_log;

Give a User Permission to schedule Jobs

grant create job to USER_NAME;

Create a Job

/* Run this job every 5 minutes except between 11am and 5pm) */
begin
dbms_scheduler.create_job (
job_name => ‘test_job_01′,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN update test1 set col1 = col1 + 1; END;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘freq=minutely; byhour=17,18,19,20,21,22,23,00,01,02,03,04,05,06,07,08,09,10; interval=5′,
end_date => NULL,
enabled => TRUE,
comments => ‘Job to test use of dbms_scheduler’);
end;
/

Alter a Job

begin
dbms_scheduler.set_attribute (
name => ‘test_job_01′,
attribute => ‘repeat_interval’,
value => ‘freq=minutely; interval=5′);
end;
/

Run a Job

/* in current session */
exec dbms_scheduler.run_job (’test_job_01′);

/* in separate session */
exec dbms_scheduler.run_job (’test_job_01′, FALSE);

Disable a Job

exec dbms_scheduler.disable (’test_job_01′);

/* if the job is currently running */
exec dbms_scheduler.disable (’test_job_01′, TRUE);

Enable a Job

exec dbms_scheduler.enable (’test_job_01′);

Stop a Running Job

exec dbms_scheduler.stop_job (’test_job_01′);

Remove a Job

exec dbms_scheduler.drop_job (’test_job_01′);