Why Oracle DBMS_JOB Shows Next Date 4000‑01‑01 and How to Fix It

Background

A colleague asked whether the job stored in PKG_WMS.proc_TaskMain was still running. The query on dba_jobs showed NEXT_DATE = 4000/1/1, which signals a problem.

Job Parameters

BROKEN : interruption flag (‘N’ for running, ‘Y’ for broken). Use DBMS_JOBS.BROKEN(job_id,TRUE/FALSE) to stop/start the job, then commit.

FAILURES : number of consecutive errors.

last_date : timestamp of the last successful execution.

next_date : scheduled next run (affected by last_date and the interval).

total_time : cumulative runtime of the job.

this_date : timestamp while the job is currently executing.

Analysis of Failure

If the stored procedure invoked by the job throws an exception (e.g., insufficient tablespace, PL/SQL syntax error), Oracle automatically retries up to 16 times. After 16 consecutive failures the job is marked broken (BROKEN = ‘Y’) and NEXT_DATE is set to 4000‑01‑01. The job must then be started manually with DBMS_JOB.RUN(:id).

Example: a procedure p1 with a deliberate syntax error will cause the job to run 16 times, then become broken. If the procedure is corrected before reaching 16 attempts, the failure count resets to zero and the job resumes normally.

Diagnostic Steps

Manually execute the stored procedure to see if it raises an error. If it runs without error, the job has already failed 16 times and needs a manual restart:

BEGIN
  DBMS_JOB.RUN(1543);
END;
/

Check the Oracle alert log; job‑related exceptions are recorded there.

Sample Alert Log Errors

ORA-12012 : 自动执行作业 1543 出错
ORA-12899 : 列 "FWS"."RECODE_ERROR_MSG"."ERROR_MSG" 的值太大 (实际值 : 704, 最大值: 500 )
ORA-01688 : 表 FWS.RECODE_ERROR_MSG 分区 SYS_P6181 无法通过 8192 在表空间 TBS_WMS_CITY_JK_DATA 中扩展
ORA-01400 : 无法将 NULL 插入 ("FWS"."BILL_RECEIPT_CITY"."CREATOR")
ORA-1688 : unable to extend table FWS.RECODE_ERROR_MSG partition SYS_P6181 by 8192 in tablespace TBS_WMS_CITY_JK_DATA

Resolution

Space shortage: The RECODE_ERROR_MSG table accumulates ~100 million rows daily, exhausting tablespace. Truncate the table with TRUNCATE TABLE RECODE_ERROR_MSG; to free space.

Validate the stored procedure by deliberately causing it to fail, then capture the error messages from the alert log to understand the root cause.