How to Fix Broken Oracle DBMS_JOB Jobs (ORA-06550 & PLS-00302) — Complete Guide

Oracle database jobs are essential for automating background tasks such as data processing, cleanup routines, and system maintenance. However, when these jobs fail repeatedly, Oracle marks them as BROKEN, stopping further execution. This can silently disrupt business operations if not addressed quickly.

In this guide, we’ll walk through a real-world scenario where multiple DBMS_JOB jobs failed due to a simple but critical mistake — and how to fix them properly.

---

The Problem: Broken Jobs in Oracle

You may encounter jobs with the following symptoms:

• BROKEN = Y
• FAILURES = 16
• NEXT_DATE = 01-JAN-00

Additionally, when running the job manually, you might see errors like:

ORA-06550: line 2, column 28:
PLS-00302: component must be declared

This indicates that Oracle cannot find the procedure being called by the job.

---

Root Cause: Incorrect Procedure Name

In this case, the jobs were calling:

PK_PQ_BROWSER_CRASH_APPS.PU_UPDATE_PO_MAIN;

But the actual procedure available in the package was:

PU_UPDATE_PQ_MAIN

👉 A small typo (PO vs PQ) caused:

• Job execution failures
• Failure count reaching 16
• Oracle marking jobs as BROKEN
• Execution being stopped permanently

---

Step-by-Step Fix for Broken Jobs

🔧 Step 1: Update Job Definitions (Critical Fix)

First, correct the procedure name inside each job.

BEGIN
  DBMS_JOB.WHAT(441, 'PK_PQ_BROWSER_CRASH_APPS.PU_UPDATE_PQ_MAIN;');
END;
/
COMMIT;

BEGIN
  DBMS_JOB.WHAT(564, 'PK_PQ_BROWSER_CRASH_APPS_ODS.PU_UPDATE_PQ_MAIN;');
END;
/
COMMIT;

BEGIN
  DBMS_JOB.WHAT(343, 'PK_PQ_BROWSER_CRASH_APPS_REGI.PU_UPDATE_PQ_MAIN;');
END;
/
COMMIT;

⚠️ Always verify the correct procedure name using DBA_PROCEDURES before updating jobs.

---

Step 2: Re-enable the Jobs

Once the job definitions are corrected, mark them as active:

BEGIN
  DBMS_JOB.BROKEN(441, FALSE);
  DBMS_JOB.BROKEN(564, FALSE);
  DBMS_JOB.BROKEN(343, FALSE);
END;
/
COMMIT;

---

Step 3: Reset NEXT_DATE

When a job becomes broken, Oracle often sets:

NEXT_DATE = 01-JAN-00

This prevents the job from running again.

Fix it using:

BEGIN
  DBMS_JOB.NEXT_DATE(441, SYSDATE);
  DBMS_JOB.NEXT_DATE(564, SYSDATE);
  DBMS_JOB.NEXT_DATE(343, SYSDATE);
END;
/
COMMIT;

---

Step 4: Test the Procedure Manually

Before leaving, always test the procedure:

BEGIN
  PK_PQ_BROWSER_CRASH_APPS.PU_UPDATE_PQ_MAIN;
END;
/

👉 This must run without errors.
If it fails, the job will fail again.

---

Why This Happened

Let’s summarize the failure chain:

1. A typo in the procedure name (PO instead of PQ)
2. Job execution failed repeatedly
3. Oracle counted 16 failures
4. Jobs were marked as BROKEN
5. Execution stopped (NEXT_DATE = 01-JAN-00)

---

Key Lessons Learned

✅ Always validate procedure names

Even a small typo can break automation.

✅ Monitor job failures regularly

Query:

SELECT job, failures, broken FROM dba_jobs;

✅ Test manually before enabling

Never assume the job will work after fixing.

✅ Check invalid objects

SELECT object_name, status FROM dba_objects WHERE status='INVALID';

---

Pro Tip: Consider DBMS_SCHEDULER

DBMS_JOB is a legacy feature. Modern Oracle environments use DBMS_SCHEDULER, which offers:

• Better monitoring
• Detailed logging
• Flexible scheduling
• GUI visibility in SQL Developer

Example:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'JOB_PQ_MAIN',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN PK_PQ_BROWSER_CRASH_APPS.PU_UPDATE_PQ_MAIN; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=10',
    enabled         => TRUE
  );
END;
/

---

Final Thoughts

Broken jobs in Oracle are often symptoms of deeper issues — but sometimes, the root cause can be as simple as a typo. The key is to:

• Identify the real error
• Fix the underlying issue
• Re-enable and reschedule properly

By following this structured approach, you can quickly restore job functionality and avoid future disruptions.