dbms_job vs. dbms_scheduler
Oracle Tips by Burleson Consulting
Oracle Database 10g, the Oracle scheduler was greatly improved with
the dbms_scheduler package. Replacing the dbms_job
with dbms_scheduler offers additional features by adding the
ability to tie jobs with specific user-type privileges and roles:
Comparison between dbms_job and
integration of job creation and
modification in Oracle Enterprise
and roles specifically for the scheduler
to increase control over the scheduling
Table 1.1 - Features
comparison between the dbms_job and
See from Dr. Timothy Hall's book "Oracle
these tips on migrating from dbms_job to
using dbms_job scheduler.
job => :l_job,
what => 'BEGIN NULL; /* Do Nothing */
next_date => SYSDATE,
interval => 'SYSDATE + 1 /* 1 Day */');
with dbms_scheduler scheduler.
job_name => 'dummy_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN NULL; /* Do
Nothing */ END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'SYSTIMESTAMP + 1 /* 1
these examples, it is noted that conversion of
basic jobs is quite simple, involving the
meaningful job_name for the new job.
job_action of PLSQL_BLOCK.
what value from the old job as the
job_action value in the new job.
SYSTIMESTAMP for the start_date
interval value from the old job as
the repeat_interval value in the new
job, making sure the result of the
expression is a TIMESTAMP not a DATE.
conversion has been completed for all jobs,
there is freedom from using the old scheduler,
so the job_queue_processes parameter can
now be set to zero.
The new 10g job scheduling views
Oracle MOSC also offers advice on moving from dbms_job
to dbms_scheduler and notes that the dba_jobs view
is obsolete with dbms_scheduler and we use
MOSC also notes that internally-scheduled
tasks (job queues, automatic statistics) can be seen by querying the
As the SYS user, use the following query to
check for this job:
WHERE JOB_NAME = ’GATHER_STATS_JOB’;
DBASupport article shows the main DBA Scheduler views and a
DBA_SCHEDULER_SCHEDULES - provides
me with information about the schedules that are in effect in
DBA_SCHEDULER_PROGRAMS - shows all
program objects and their attributes, while view
DBA_SCHEDULER_PROGRAM_ARGS shows all program arguments for
programs that have them.
DBA_SCHEDULER_JOBS - shows all job
objects and their attributes.