Why Do Oracle DBMS_JOBS Stop at the Year 4000? Root Cause and Fix

A colleague noticed that the job stored in PKG_WMS.proc_TaskMain had its NEXT_DATE set to 4000/1/1, indicating the job was interrupted. The article walks through the meaning of key DBMS_JOB attributes such as BROKEN (interrupt flag), FAILURES (error count), last_date , next_date , total_time , and this_date .

Automatic Failure Handling

If the stored procedure invoked by the job throws an exception—such as running out of tablespace, PL/SQL syntax errors, or other runtime problems—Oracle will automatically retry the job up to 16 consecutive failures. After the 16th failure the job’s BROKEN flag is set to Y and NEXT_DATE is changed to the sentinel value 4000‑01‑01. At this point the job must be manually restarted with DBMS_JOB.RUN(:id).

Example Scenario

Consider a procedure p1 that contains a deliberate syntax error (e.g., a stray begin/end line). The job will execute the procedure 16 times, then become broken. If the procedure is corrected before the failure count reaches 10, the FAILURES counter resets to 0 and the job resumes normal operation.

Diagnosing the Problem

Manually invoke the stored procedure. If it runs without error, the original issue has already been exhausted and the job needs a manual DBMS_JOB.RUN(:id) to restart.

Examine the Oracle alert log. Job failures are recorded there with detailed ORA‑error messages.

Attempt to Restart the Job

SQL> begin
  dbms_job.run(1543);
end;
/

The execution returns:

ORA-12011: cannot execute job 1
ORA-06512: at "SYS.DBMS_IJOB", line 648
ORA-06512: at "SYS.DBMS_JOB", line 284
ORA-06512: at line 2

Alert Log Findings

ORA-12012 : automatic execution of job 1543 failed ORA-12899 : value too large for column "FWS"."RECODE_ERROR_MSG"."ERROR_MSG" (actual: 704, maximum: 500) ORA-01688 : partition SYS_P6181 of table FWS.RECODE_ERROR_MSG could not be extended in tablespace TWS_WMS_CITY_JK_DATA ORA-01400 : cannot insert NULL into "FWS"."BILL_RECEIPT_CITY"."CREATOR"

Similar errors appear for another job (ID 26), showing missing objects and PL/SQL compilation issues.

Resolution Steps

Space shortage: The RECODE_ERROR_MSG table accumulates ~100 million rows daily. Truncating the table ( TRUNCATE TABLE RECODE_ERROR_MSG;) frees space and resolves the ORA‑01688 errors.

Other errors: Follow the messages in the alert log, correct the underlying PL/SQL code or missing objects, and then manually restart the job with DBMS_JOB.RUN.

After addressing the root causes, the job can be re‑enabled (set BROKEN to FALSE) and will resume normal scheduling.