ORA-04065: Not Executed, Altered or Dropped Stored Procedure - Fix Invalidation

ORA-04065: Not Executed, Altered or Dropped Stored Procedure

Error Overview

Error Text: ORA-04065: not executed, altered or dropped stored procedure "string.string"

The ORA-04065 error is raised when Oracle attempts to execute a stored procedure, function, package, or trigger that has been marked INVALID. This typically follows a DDL change — such as altering or dropping a table, view, type, or another PL/SQL object — that the procedure depends on. Oracle’s dependency tracking system automatically invalidates dependent objects when their dependencies change, and the invalidated object must be successfully recompiled before it can run again.

This error frequently appears alongside ORA-06508 (PL/SQL: could not find program unit being called) and is common in deployment pipelines, schema migration scripts, and environments with complex PL/SQL dependency chains.

Common Causes

1. DDL Change to a Dependent Object

• A table used by the procedure was altered (column added, dropped, or modified)
• A view referenced by the procedure was replaced or dropped
• A dependent package specification was recompiled (invalidates all its dependents)
• A synonym was dropped or redirected to a different object
• A type or object type was altered, triggering cascading invalidation

2. Object Dropped and Recreated

• A referenced table was dropped and recreated (new object ID)
• A package was dropped and recreated (resets dependent object state)
• A procedure was replaced with CREATE OR REPLACE — Oracle briefly marks its dependents INVALID while recompiling

3. Incomplete Recompilation After Deployment

• Schema migration deployed DDL changes but did not recompile dependent PL/SQL
• Parallel deployment steps altered multiple objects simultaneously
• Compilation failed due to a missing grant or privilege — object stays INVALID
• Recompilation script was skipped or failed silently

4. Privilege Revocation

• EXECUTE or SELECT privilege on a dependent object was revoked
• Role-based privileges used inside PL/SQL (definer’s rights procedures do not see role grants)
• Synonym owner lost access to the underlying object

5. Edition-Based Redefinition Side Effects

• Editioning views or editioned objects altered in a different edition
• Cross-edition triggers invalidated by base table changes
• Application running in the wrong edition after a deployment

Diagnostic Queries

Find Invalid Objects

-- All invalid objects in the database
SELECT
  owner,
  object_name,
  object_type,
  status,
  last_ddl_time,
  created
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

-- Invalid objects for a specific schema
SELECT
  object_name,
  object_type,
  status,
  TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') as last_ddl
FROM dba_objects
WHERE owner  = 'YOUR_SCHEMA'
  AND status = 'INVALID'
ORDER BY object_type, object_name;

Identify Dependency Chains

-- Direct dependencies of a specific object
SELECT
  name          as dependent_object,
  type          as dependent_type,
  referenced_owner,
  referenced_name,
  referenced_type,
  dependency_type
FROM dba_dependencies
WHERE name             = 'YOUR_PROCEDURE_NAME'
  AND owner            = 'YOUR_SCHEMA'
ORDER BY referenced_type, referenced_name;

-- Find all objects that depend ON a recently changed object
SELECT
  d.owner,
  d.name          as dependent_object,
  d.type          as dependent_type,
  o.status
FROM dba_dependencies d
JOIN dba_objects o
  ON  o.owner       = d.owner
  AND o.object_name = d.name
  AND o.object_type = d.type
WHERE d.referenced_owner = 'YOUR_SCHEMA'
  AND d.referenced_name  = 'CHANGED_TABLE_OR_PACKAGE'
ORDER BY d.type, d.name;

-- Full dependency tree (multi-level) using connect by
SELECT LEVEL,
  LPAD(' ', (LEVEL-1) * 2) || d.name as object_name,
  d.type,
  d.owner,
  o.status
FROM dba_dependencies d
JOIN dba_objects o
  ON  o.owner       = d.owner
  AND o.object_name = d.name
  AND o.object_type = d.type
START WITH
  d.referenced_owner = 'YOUR_SCHEMA'
  AND d.referenced_name = 'CHANGED_OBJECT'
CONNECT BY PRIOR d.owner = d.referenced_owner
       AND PRIOR d.name  = d.referenced_name
ORDER SIBLINGS BY d.name;

Check Compilation Errors

-- Errors from the most recent failed compilation attempt
SELECT
  owner,
  name,
  type,
  line,
  position,
  text as error_message
FROM dba_errors
WHERE owner = 'YOUR_SCHEMA'
ORDER BY owner, name, sequence;

-- Get error for a specific object
SELECT line, position, text
FROM dba_errors
WHERE owner = 'YOUR_SCHEMA'
  AND name  = 'YOUR_PROCEDURE_NAME'
  AND type  = 'PROCEDURE'
ORDER BY sequence;

Count Invalid Objects by Type

-- Summary of invalid objects by type and owner
SELECT
  owner,
  object_type,
  COUNT(*) as invalid_count
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type
ORDER BY owner, invalid_count DESC;

Step-by-Step Resolution

1. Attempt Automatic Recompilation

Oracle will automatically attempt to recompile an invalid object the first time it is called. If dependencies are satisfied, this succeeds transparently. If it fails, ORA-04065 is returned to the caller. To test this manually:

-- Attempt to manually recompile a specific object
ALTER PROCEDURE your_schema.your_procedure COMPILE;
ALTER FUNCTION  your_schema.your_function  COMPILE;
ALTER PACKAGE   your_schema.your_package   COMPILE;
ALTER PACKAGE   your_schema.your_package   COMPILE BODY;
ALTER TRIGGER   your_schema.your_trigger   COMPILE;
ALTER TYPE      your_schema.your_type      COMPILE;

-- Recompile a view
ALTER VIEW your_schema.your_view COMPILE;

2. Recompile All Invalid Objects in a Schema

-- Use DBMS_UTILITY to recompile all invalid objects in a schema
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'YOUR_SCHEMA', compile_all => FALSE);
-- compile_all => FALSE recompiles only INVALID objects (faster)
-- compile_all => TRUE  recompiles ALL objects regardless of status

-- Verify results
SELECT object_type, COUNT(*) as invalid_count
FROM dba_objects
WHERE owner  = 'YOUR_SCHEMA'
  AND status = 'INVALID'
GROUP BY object_type;

3. Recompile All Invalid Objects Database-Wide

-- UTL_RECOMP recompiles invalid objects in parallel (Oracle 10g+)
-- Recommended approach for large schemas after major deployments

-- Sequential recompilation (safer, good for smaller schemas)
EXEC UTL_RECOMP.RECOMP_SERIAL();

-- Parallel recompilation using 4 parallel workers
EXEC UTL_RECOMP.RECOMP_PARALLEL(4);

-- Recompile a single schema in parallel
EXEC UTL_RECOMP.RECOMP_PARALLEL(4, 'YOUR_SCHEMA');

-- After running, check for any remaining invalid objects
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

4. Fix Compilation Errors

If recompilation fails, examine the errors and fix the root cause:

-- Check what errors are preventing compilation
SELECT line, position, text
FROM dba_errors
WHERE owner = 'YOUR_SCHEMA'
  AND name  = 'PROBLEM_PROCEDURE'
ORDER BY sequence;

Common fixes after reviewing errors:

-- Missing privilege: grant required privilege
GRANT SELECT ON base_table TO your_schema;

-- Missing synonym: recreate it
CREATE OR REPLACE SYNONYM your_schema.missing_synonym FOR other_schema.target_table;

-- Dropped column: update procedure to remove reference to dropped column
-- (requires editing the procedure source code)

-- After fixing, recompile
ALTER PROCEDURE your_schema.problem_procedure COMPILE;

5. Avoid Invalidation During Deployment with Fine-Grained Dependency Tracking

Oracle 11g+ introduced fine-grained dependency tracking. For tables, adding a new column no longer invalidates procedures that do not reference that column. However, dropping a column, changing a data type, or altering a package specification still triggers invalidation. Design deployments to minimize cascading impacts:

-- Check if a table change will invalidate dependents:
-- Adding a column (safe in 11g+ with fine-grained tracking)
ALTER TABLE your_table ADD new_column VARCHAR2(100);

-- Verify dependent objects remain valid
SELECT object_name, object_type, status
FROM dba_objects
WHERE object_name IN (
  SELECT name FROM dba_dependencies
  WHERE referenced_name = 'YOUR_TABLE'
    AND referenced_owner = 'YOUR_SCHEMA'
)
AND status = 'INVALID';

6. Edition-Based Redefinition (EBR) Considerations

-- Check current edition
SELECT sys_context('USERENV','CURRENT_EDITION_NAME') FROM dual;

-- List all editions
SELECT edition_name, parent_edition_name, usable FROM dba_editions;

-- Check which edition an object belongs to
SELECT object_name, object_type, edition_name, status
FROM dba_objects_ae
WHERE object_name = 'YOUR_PROCEDURE'
  AND owner       = 'YOUR_SCHEMA'
ORDER BY edition_name;

-- Recompile in a specific edition
ALTER SESSION SET EDITION = your_edition_name;
ALTER PROCEDURE your_schema.your_procedure COMPILE;

Prevention Strategies

1. Post-Deployment Recompilation Script

-- Standard post-deployment recompilation procedure
CREATE OR REPLACE PROCEDURE post_deploy_recompile AS
  v_invalid_count NUMBER;
  v_pass          NUMBER := 0;
  v_max_passes    NUMBER := 3;
BEGIN
  LOOP
    v_pass := v_pass + 1;
    UTL_RECOMP.RECOMP_PARALLEL(4);

    SELECT COUNT(*) INTO v_invalid_count
    FROM dba_objects
    WHERE status = 'INVALID'
      AND object_type IN ('PROCEDURE','FUNCTION','PACKAGE',
                          'PACKAGE BODY','TRIGGER','TYPE','VIEW');

    EXIT WHEN v_invalid_count = 0 OR v_pass >= v_max_passes;

    DBMS_OUTPUT.PUT_LINE('Pass ' || v_pass || ': ' ||
      v_invalid_count || ' objects still invalid, retrying...');
  END LOOP;

  IF v_invalid_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: ' || v_invalid_count ||
      ' objects remain invalid after ' || v_pass || ' passes.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('All objects valid after ' || v_pass || ' pass(es).');
  END IF;
END;
/

2. Monitor Object Validity Continuously

-- Alert on newly invalidated objects
CREATE OR REPLACE PROCEDURE alert_on_invalid_objects AS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count
  FROM dba_objects
  WHERE status = 'INVALID'
    AND object_type IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY',
                        'TRIGGER','TYPE','TYPE BODY');

  IF v_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('ALERT: ' || v_count ||
      ' invalid PL/SQL object(s) detected. Investigate immediately.');
  END IF;
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'ALERT_INVALID_OBJECTS',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'alert_on_invalid_objects',
    repeat_interval => 'FREQ=HOURLY',
    enabled         => TRUE
  );
END;
/

3. Best Practices to Minimise Invalidation Cascades

• Always add new columns rather than modifying existing ones where possible
• Change package specifications only when the public interface genuinely changes; move implementation details to the package body
• Use CREATE OR REPLACE rather than DROP + CREATE for PL/SQL objects
• After any DDL deployment, run UTL_RECOMP.RECOMP_PARALLEL before opening the application to traffic
• In CI/CD pipelines, add a post-migration step that asserts zero invalid objects before marking a deployment successful
• Use AUTHID CURRENT_USER (invoker’s rights) carefully — privilege resolution differs from definer’s rights and can cause unexpected invalidation

Diagnostic Scripts

These Oracle Day by Day scripts can help investigate object dependencies and schema health:

• health.sql — Overall database health check including invalid object counts
• dtable.sql — Table structure analysis useful when investigating column-level dependency changes

Related Errors

• ORA-06508 - PL/SQL: could not find program unit being called
• ORA-06512 - At line (PL/SQL error stack backtrace)
• ORA-00942 - Table or view does not exist
• ORA-01031 - Insufficient privileges

Emergency Response

Quick Fixes

1. Immediately recompile the offending object

   ALTER PROCEDURE schema_name.procedure_name COMPILE;
   -- Check for errors:
   SELECT line, text FROM dba_errors
   WHERE name = 'PROCEDURE_NAME' AND owner = 'SCHEMA_NAME'
   ORDER BY sequence;

2. Recompile all invalid objects in the schema

   EXEC DBMS_UTILITY.COMPILE_SCHEMA('YOUR_SCHEMA', FALSE);

3. Database-wide parallel recompile during a maintenance window

   EXEC UTL_RECOMP.RECOMP_PARALLEL(4);

Post-Resolution Cleanup

-- Confirm all critical objects are valid
SELECT
  owner,
  object_name,
  object_type,
  status,
  TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') as last_ddl
FROM dba_objects
WHERE status = 'INVALID'
  AND object_type IN ('PROCEDURE','FUNCTION','PACKAGE',
                      'PACKAGE BODY','TRIGGER','TYPE')
ORDER BY owner, object_type, object_name;

-- Review what DDL changes triggered the invalidation
SELECT
  owner,
  object_name,
  object_type,
  TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') as last_ddl_time
FROM dba_objects
WHERE last_ddl_time > SYSDATE - 1/24  -- Changed in last hour
ORDER BY last_ddl_time DESC;