Oracle DBMS_SCHEDULER vs DBMS_JOB (Create, Run, Monitor, Remove)

Ben MarckTechnical TipsLeave a Comment

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

Leave a Reply

Your email address will not be published. Required fields are marked *