Oracle ORA-04064 Error: Causes and Solutions Complete Guide

ORA-04064: not executed, invalidated — What It Means and How to Fix It

ORA-04064 is an Oracle error that occurs when a PL/SQL object (stored procedure, function, package, or trigger) has been marked INVALID and cannot be executed. This typically happens when a dependent object — such as a table, view, or another procedure — is modified or dropped, causing Oracle to automatically invalidate any PL/SQL objects that reference it. Left unresolved, this error can bring down application functionality across your entire system.

---

Top 3 Causes

1. DDL Changes on Dependent Objects

The most common cause. When you run ALTER TABLE, DROP TABLE, or CREATE OR REPLACE VIEW on an object that a PL/SQL unit references, Oracle automatically flags that unit as INVALID.

-- Example: Adding a column invalidates dependent procedures
ALTER TABLE employees ADD (department_code VARCHAR2(10));

-- Check what just became INVALID
SELECT object_name, object_type, status
FROM   user_objects
WHERE  status = 'INVALID'
ORDER  BY object_type, object_name;

2. Dropping or Replacing Referenced PL/SQL Units

When a procedure, function, or package that is called by another PL/SQL object is dropped or re-created with CREATE OR REPLACE, all referencing objects are cascade-invalidated.

-- Replacing a package spec invalidates all callers
CREATE OR REPLACE PACKAGE my_utils AS
    PROCEDURE new_procedure(p_id IN NUMBER);
    -- Adding this breaks all objects that depend on the original spec
END my_utils;
/

-- Find all objects that depend on a given package
SELECT name, type, referenced_name
FROM   user_dependencies
WHERE  referenced_name = 'MY_UTILS'
  AND  referenced_type = 'PACKAGE';

3. Privilege Revocation or Synonym Changes

Revoking an EXECUTE privilege or dropping/modifying a synonym that a PL/SQL object relies on will also cause ORA-04064.

-- Revoking a privilege silently invalidates dependent objects
REVOKE EXECUTE ON schema_a.utility_pkg FROM schema_b;

-- Verify affected objects in schema_b
SELECT object_name, object_type, status
FROM   all_objects
WHERE  owner  = 'SCHEMA_B'
  AND  status = 'INVALID';

---

Quick Fix Solutions

Option 1 — Recompile individual objects:

ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION  my_function  COMPILE;
ALTER PACKAGE   my_package   COMPILE SPECIFICATION;
ALTER PACKAGE   my_package   COMPILE BODY;
ALTER TRIGGER   my_trigger   COMPILE;

Option 2 — Bulk recompile with UTL_RECOMP (recommended for mass invalids):

-- Sequential recompile for one schema
EXEC UTL_RECOMP.RECOMP_SERIAL('MY_SCHEMA');

-- Parallel recompile using 4 threads
EXEC UTL_RECOMP.RECOMP_PARALLEL(4, 'MY_SCHEMA');

Option 3 — Run utlrp.sql as SYSDBA for database-wide fix:

-- Connect as SYSDBA, then run:
@?/rdbms/admin/utlrp.sql

-- Verify results after recompile
SELECT object_type,
       COUNT(*)                                              AS total,
       SUM(CASE WHEN status='INVALID' THEN 1 ELSE 0 END)   AS invalid
FROM   dba_objects
WHERE  object_type IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')
GROUP  BY object_type;

If an object stays INVALID after recompile, check for compilation errors:

SELECT line, position, text
FROM   user_errors
WHERE  name = 'MY_OBJECT_NAME'
ORDER  BY sequence;

---

Prevention Tips

1. Automate recompilation in your deployment pipeline.
Always include a UTL_RECOMP or utlrp.sql step after any DDL change in your CI/CD scripts, followed by a validation query that asserts zero INVALID objects before the deployment is considered successful.

-- Post-deployment validation gate
SELECT COUNT(*) AS must_be_zero
FROM   user_objects
WHERE  status = 'INVALID';

2. Analyze dependencies before running DDL.
Use USER_DEPENDENCIES to map the blast radius of any DDL change before applying it to production, and always test in a staging environment first.

-- Pre-DDL impact analysis
SELECT name, type
FROM   user_dependencies
WHERE  referenced_name = 'TARGET_TABLE_OR_OBJECT'
ORDER  BY type, name;

---

Related Errors

Error Code	Description
ORA-04061	Existing state of a package has been invalidated; session must re-initialize it
ORA-04065	Not executed — the stored procedure was altered or dropped
ORA-06508	PL/SQL program unit could not be found (often follows mass invalidation)
ORA-00942	Table or view does not exist — surfaces during recompile if base objects are missing

---

📖 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.