DBMS_SCHEDULER is a newer, more complex job scheduling engine released in 10g, intended to replace DBMS_JOB going forward. DBMS_JOB, the older job scheduler, as of now is still available for quick and easy DB based job scheduling.
Job scheduling comprises the core of the functionality of DBMS_SCHUDULER, however here are some additional benefits available to DBMS_SCHEDULER:
- Logging of job runs (job history)
- Simple but powerful scheduling syntax (similar to but more powerful than cron syntax)
- Running of jobs outside of the database on the operating system (see below)
- Resource management between different classes of jobs
- Use of job arguments including passing of objects into stored procedures
- Privilege-based security model for jobs
- Naming of jobs and comments in jobs
- Stored, reusable schedules
Now, let’s match up similar functionality across both engines.
Create a Job (PL/SQL or DB jobs)
BEGIN DBMS_SCHEDULER.create_job ( job_name => 'test_full_job_definition', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, enabled => TRUE, comments => 'Job defined entirely by the CREATE JOB procedure.'); END;
DECLARE l_job NUMBER := 0; BEGIN DBMS_JOB.SUBMIT(l_job,'procedure_name;',sysdate,'TRUNC(sysdate)+1+1/288'); END;
Run a Job
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'MY_JAVA_JOB';
SELECT job, what, next_date, next_sec FROM user_jobs;
Remove a Job
DBMS_SCHEDULER has the ability to schedule a job to run outside of the DB. The following will run the myClass java executable every minute.
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'my_java_job', job_type => 'EXECUTABLE', job_action => '/usr/bin/java myClass', repeat_interval => 'FREQ=MINUTELY', enabled => TRUE ); END;
'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;', 'freq=MINUTELY;interval=1', 'freq=MINUTELY;interval=5', 'freq=HOURLY;interval=1', 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',