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:
|
See from Dr. Timothy Hall's book "Oracle Job Scheduling" these tips on migrating from dbms_job to dbms_scheduler:
|
|
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 dba_scheduler_jobs:
select job_name, enabled from user_scheduler_jobs; and this: selectjob_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;
MOSC 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.