Although running jobs via DBMS_JOB has been deprecated with the introduction of the Scheduler in Oracle 10g, there are still many systems that contain jobs configured in this way. In fact, there are still an awful lot of developers and DBA’s who continue to use DBMS_JOB because it’s simpler and they are more familiar with it.
Having said that, there are still several reasons why a DBMS_JOB will not run, including a few Oracle bugs which are nicely documented on Metalink in Note 313102.1. Included within this note (applicable to versions 184.108.40.206 to 10.1.0.2) is a handy troubleshooting guide which describes the most common reasons for jobs failing to run which I have reproduced below:
Checklist For Job Issues:
- Is the Database Instance in RESTRICTED SESSIONS mode?
- Is the parameter JOB_QUEUE_PROCESSES set to 0?
- Is the hidden parameter '_SYSTEM_TRIG_ENABLED' set to FALSE?
- Is the job marked 'BROKEN' in the DBA_JOBS table?
- Have you issued a commit after submitting the job?
- Have you tried setting the hidden parameter, "_job_queue_interval" to a different value (default is 5 seconds)?
- Has the database server (machine) been up for more than 497 days
- Check Internal Oracle bug: 3427424 "SLGCSF / SLGCS STOP INCREMENTING AFTER 497 DAYS UPTIME"
- It is fixed in 10.1.0.2.0. - workaround is to reboot of the database server.
- Is the job still running in DBA_JOBS_RUNNING?
- Does the LAST_DATE and NEXT_DATE fields in DBA_JOBS make sense for the particular job?
- If LAST_DATE is null, the job has never executed automatically.
- Does the NEXT_DATE field change per the INTERVAL field in DBA_JOBS?
- If not, it's not automatically working?
- Have you tried changing the value for JOB_QUEUE_PROCESSES to '0' and then back again?
- This restarts the CJQ process.
- See Oracle Bug 2649244 (fixed by: 9015, 9203, 10201)
- Finally, if you have upgraded or refreshed the database, try executing 'exec dbms_ijob.set_enabled(true);' as sysadm.
If all else fails, try rebooting the database server (not the database instance) as the infamous 497 days bug is misleadingly titled and can in fact sometimes manifest much earlier.