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)

DBMS_SCHEDULER

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;

DBMS_JOB

DECLARE
  l_job NUMBER := 0;
BEGIN
  DBMS_JOB.SUBMIT(l_job,'procedure_name;',sysdate,'TRUNC(sysdate)+1+1/288');
END;

Run a Job

DBMS_SCHUDULER

EXEC dbms_scheduler.run_job('myjob');

DBMS_JOB

EXEC dbms_job.run(job_no);

Monitor Jobs

DBMS_SCHEDULER

SELECT * FROM dba_scheduler_jobs WHERE job_name = 'MY_JAVA_JOB';

DBMS_JOB

SELECT job, what, next_date, next_sec FROM user_jobs;

Remove a Job

DBMS_SCHEDULER

EXEC DBMS_SCHEDULER.DROP_JOB('my_java_job');

DBMS_JOB

EXECUTE DBMS_JOB.REMOVE(jobno);

DBMS_SCHEDULER Functionality

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;

REPEAT_INTERVAL Examples

'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;',

Related Information:

1. SQL to Query Oracle, Return XML - with SQLX

   Like many days, I had a need to query data stored in Oracle. But today was different. I needed to...

2. Oracle BPEL Email Notification - Remove Attachment

   If you have used the email notification activity in Oracle BPEL (up through the latest version at the time of...

3. Installing APEX 4 on Windows Server 2003 64 bit edition with Oracle Database 11g

   Overview
    This tutorial article and it's associated video will help guide you through the process of installing APEX version 4.0...

4. MedPivot Launched - Medical Jobs, Careers, and Resources with Web 2.0

   M&S Consulting is excited to announce that we have launched MedPivot.com, a resource for Medical Jobs, Careers, and Resources. The...

5. Oracle Forms - Remove List Element from List Item

   This one is a little wily, so John suggested we post it. Removing a label/value from a dropdown should be...

6. Conditionally Purge BPEL Instances on 10.1.3.x

   Marc Kelderman wrote a helpful article on this topic: http://orasoa.blogspot.com/2007/03/delete-bpel-instances.html One of the users there notes: Note there is a...

7. Oracle Materialized View DDL

   Are you a TOAD fanatic when it comes to “anything-Oracle”? If so, you probably realized that TOAD will not show...

8. Oracle INSTR Function - SQL Syntax Examples

   The Oracle INSTR SQL function is popular and performs materially the same operation as instr functions in many other programming...