Oracle ORA-12012 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12012: Error on Auto Execute of Job — Causes, Fixes & Prevention

What Is ORA-12012?

ORA-12012 is a wrapper error raised by Oracle's job execution engine (DBMS_JOB or DBMS_SCHEDULER) when an automatically scheduled job fails during execution. It rarely appears alone — you'll almost always see it accompanied by secondary errors like ORA-06512, ORA-01031, or ORA-01555 in the alert.log or trace files, which reveal the true root cause. Understanding ORA-12012 requires analyzing the full error stack, not just the top-level message.

Top 3 Causes

1. INVALID Stored Procedures or Packages Referenced by the Job

When a DDL change (e.g., adding/dropping a column) invalidates a stored procedure or package that a job depends on, the job will fail at runtime with ORA-12012. Oracle automatically invalidates dependent objects after DDL operations, so any job pointing to an INVALID object will immediately error out.

-- Check for INVALID objects in the job owner's schema
SELECT owner, object_name, object_type, status, last_ddl_time
FROM dba_objects
WHERE status = 'INVALID'
  AND owner = 'SCOTT'
ORDER BY object_type, object_name;

-- Recompile an invalid procedure
ALTER PROCEDURE scott.my_job_proc COMPILE;

-- Bulk recompile an entire schema
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'SCOTT', compile_all => FALSE);

2. Insufficient Privileges on the Job Execution Account

Privileges granted via a Role are not visible inside stored procedures running with Definer's Rights. If the job's owner account relies on role-based privileges to access tables or packages, the job will fail with ORA-01031 (insufficient privileges), which surfaces as ORA-12012.

-- Check direct privileges granted to the job owner
SELECT grantee, owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee = 'SCOTT';

-- Grant privileges directly (not via role) to fix the issue
GRANT SELECT, INSERT, UPDATE ON hr.employees TO scott;
GRANT EXECUTE ON sys.dbms_lock TO scott;

-- Check if DBMS_JOB job is BROKEN due to repeated failures
SELECT job, what, broken, failures, last_date, next_date
FROM dba_jobs
WHERE broken = 'Y';

-- Re-enable a broken job
BEGIN
  DBMS_JOB.BROKEN(job => 101, broken => FALSE, next_date => SYSDATE);
  COMMIT;
END;
/

3. Unhandled Exceptions Inside the Job Logic

If the procedure called by the job raises an unhandled exception — such as a division by zero, NO_DATA_FOUND, or a data integrity issue — Oracle's job engine catches it and logs ORA-12012. In DBMS_JOB, repeated failures increment the FAILURES counter, and after 16 failures the job is automatically marked BROKEN and stops running.

-- Check recent job run failures (DBMS_SCHEDULER)
SELECT job_name, log_date, status, error#, additional_info
FROM dba_scheduler_job_run_details
WHERE status = 'FAILED'
  AND log_date >= SYSDATE - 7
ORDER BY log_date DESC;

-- Example: Procedure with proper exception handling
CREATE OR REPLACE PROCEDURE scott.my_job_proc AS
  v_error_msg VARCHAR2(4000);
BEGIN
  -- Main business logic here
  INSERT INTO scott.job_log (run_date, status)
  VALUES (SYSDATE, 'RUNNING');
  COMMIT;

  -- ... actual work ...

EXCEPTION
  WHEN OTHERS THEN
    v_error_msg := SQLERRM || CHR(10)
                   || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
    INSERT INTO scott.job_error_log (log_date, proc_name, error_msg)
    VALUES (SYSDATE, 'MY_JOB_PROC', v_error_msg);
    COMMIT;
    RAISE; -- Re-raise so the scheduler marks the job as FAILED
END my_job_proc;
/

Quick Fix Solutions

-- Re-enable a disabled DBMS_SCHEDULER job
BEGIN
  DBMS_SCHEDULER.ENABLE(name => 'SCOTT.MY_SCHEDULER_JOB');
END;
/

-- Manually run a DBMS_SCHEDULER job to test it
BEGIN
  DBMS_SCHEDULER.RUN_JOB(job_name => 'SCOTT.MY_SCHEDULER_JOB');
END;
/

-- View full error details for DBMS_SCHEDULER jobs
SELECT job_name, log_date, status, error#, additional_info
FROM dba_scheduler_job_run_details
WHERE job_name = 'MY_SCHEDULER_JOB'
ORDER BY log_date DESC
FETCH FIRST 10 ROWS ONLY;

Prevention Tips

1. Automate job failure monitoring. Set up a daily monitoring query or an OEM alert that checks DBA_SCHEDULER_JOB_RUN_DETAILS and DBA_JOBS for failures. Notify the DBA team when the failure count exceeds a threshold (e.g., 3 consecutive failures) before the job goes BROKEN.

2. Include INVALID object checks in every deployment. After any DDL change in production, always run DBMS_UTILITY.COMPILE_SCHEMA or utlrp.sql, and verify that zero INVALID objects remain before signing off on the deployment. Make this a mandatory step in your change management checklist.

Related Oracle Errors

Error Code	Description
ORA-06512	PL/SQL stack trace — shows exact line number of failure
ORA-01031	Insufficient privileges — common companion to ORA-12012
ORA-04068	Package state discarded — triggers ORA-12012 on next job run
ORA-01555	Snapshot too old — seen in long-running batch jobs
ORA-12011	Multiple jobs failed — similar to ORA-12012 but for batch failures

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community