Why Do Oracle DBMS_JOBS Show a 4000‑01‑01 Next Date? Debugging Stuck Jobs

When a colleague queried the PKG_WMS.proc_TaskMain job, the dba_jobs.NEXT_DATE field showed the impossible value 4000/1/1. The article walks through the meaning of the job’s status columns and why this occurs.

Job status fields

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

FAILURES : number of consecutive failures.

last_date : timestamp of the last successful run.

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

total_time : cumulative execution time.

this_date : timestamp while the job is currently executing.

Analysis of the 4000‑01‑01 issue

If the stored procedure called by the job throws an exception (e.g., out‑of‑space, PL/SQL errors), the job will automatically retry up to 16 times. After 16 consecutive failures the job is marked BROKEN = Y and next_date is set to 4000‑01‑01. The job must then be restarted manually with DBMS_JOB.RUN(:id).

Example: a procedure p1 with a syntax error will cause the job to fail 16 times, become broken, and stop. If the procedure is corrected before reaching 16 failures, the failure count resets to 0 and the job resumes.

Diagnosing the problem

Manually execute the stored procedure. If it runs without error, the previous failures were due to the procedure itself and the job now needs a manual restart:

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

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

Typical error output from the manual run:

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

Further alert‑log excerpts may show errors such as:

ORA-12012: automatic execution of job 1543 failed
ORA-12899: value too large for column "FWS"."RECODE_ERROR_MSG"."ERROR_MSG" (actual: 704, max: 500)
ORA-01688: unable to extend table FWS.RECODE_ERROR_MSG partition SYS_P6181 in tablespace TBS_WMS_CITY_JK_DATA

Resolving the issue

Space shortage: the RECODE_ERROR_MSG table accumulates massive daily data. Truncate the table to free space.

Validate and fix the stored procedure code to prevent repeated failures.

After addressing the root cause, manually restart the job with DBMS_JOB.RUN(job_id) and commit.

Following these steps restores the job’s normal schedule and removes the erroneous 4000‑01‑01 next date.