| |
 |
|
dbms_job vs. dbms_scheduler
Oracle Tips by Burleson Consulting |
Starting with
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:
Features
Comparison between dbms_job and
dbms_scheduler
|
Feature |
dbms_job |
dbms_scheduler |
|
Provide full
integration of job creation and
modification in Oracle Enterprise
Manager. |
Yes |
Yes |
|
Provide privileges
and roles specifically for the scheduler
to increase control over the scheduling
of jobs. |
No |
Yes |
Table 1.1 - Features
comparison between the dbms_job and
dbms_scheduler package |
|
See from Dr. Timothy Hall's book "Oracle
Job Scheduling"
these tips on migrating from dbms_job to
dbms_scheduler:
|
-- Old
using dbms_job scheduler.
VARIABLE l_job
NUMBER;
BEGIN
DBMS_JOB.submit (
job => :l_job,
what => 'BEGIN NULL; /* Do Nothing */
END;',
next_date => SYSDATE,
interval => 'SYSDATE + 1 /* 1 Day */');
COMMIT;
END;
/
PRINT l_job |
|
|
-- New
with dbms_scheduler scheduler.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'dummy_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN NULL; /* Do
Nothing */ END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'SYSTIMESTAMP + 1 /* 1
Day */');
END;
/
By comparing
these examples, it is noted that conversion of
basic jobs is quite simple, involving the
following steps:
-
Define a
meaningful job_name for the new job.
-
Assign a
job_action of PLSQL_BLOCK.
-
Use the
what value from the old job as the
job_action value in the new job.
-
Use
SYSTIMESTAMP for the start_date
value.
-
Use the
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.
Once this
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 Metalink 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
dba_scheduler_jobs:
select
job_name,
enabled
from
user_scheduler_jobs;
and this:
select
job_id,
freq_type,
freq_interval,
freq_subday_type,
freq_subday_interval,
freq_relative_interval,
freq_recurrence_factor,
active_start_date,
active_end_date,
active_start_time,
active_end_time,
schedule_id
from
dba_scheduler_jobs;
Metalink also notes that internally-scheduled
tasks (job queues, automatic statistics) can be seen by querying the
dba_scheduler_jobs view:
As the SYS user, use the following query to
check for this job:
SELECT STATE
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = ’GATHER_STATS_JOB’;
This
DBASupport article shows the main DBA Scheduler views and a
handy query:
-
DBA_SCHEDULER_SCHEDULES - provides
me with information about the schedules that are in effect in
the database.
-
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.
|
