ORA-04063: Object Has Errors - Fix Invalid PL/SQL Compilation

ORA-04063: Object Has Errors

Error Overview

Error Text: ORA-04063: [object_name] has errors

This error occurs when you attempt to execute or reference a PL/SQL object (package, package body, procedure, function, trigger, or view) that is in an INVALID state due to compilation errors. Oracle cannot run the object because it failed its most recent compilation, either explicitly or implicitly during dependency resolution.

Understanding the Error

How Oracle Invalidation Works

DDL Change (e.g., ALTER TABLE)
    │
    ▼
Dependent objects marked INVALID
    │
    ▼
Next execution triggers automatic recompilation
    │
    ├── Recompilation succeeds → Object runs normally
    │
    └── Recompilation fails → ORA-04063 raised

Common Scenarios

• After DDL changes - A table column was dropped or renamed, breaking dependent procedures
• After database upgrades - Packages reference deprecated features or changed system views
• After patching - Oracle internal packages changed, invalidating dependent objects
• Missing grants - A privilege was revoked, causing compilation to fail
• 23ai migration - Non-CDB to CDB/PDB migration altering schema visibility or deprecated syntax
• Missing synonyms - Public synonyms dropped or recreated pointing elsewhere
• Dropped dependent objects - A type, sequence, or table no longer exists

Diagnostic Steps

1. Find the Compilation Errors

-- Show compilation errors for a specific object
SELECT
  line,
  position,
  text AS error_message,
  attribute
FROM dba_errors
WHERE owner = 'SCHEMA_NAME'
  AND name = 'OBJECT_NAME'
ORDER BY sequence;

-- Find all objects with compilation errors
SELECT
  owner,
  name,
  type,
  line,
  position,
  text AS error_message
FROM dba_errors
WHERE owner NOT IN ('SYS', 'SYSTEM', 'MDSYS', 'XDB', 'WMSYS')
ORDER BY owner, name, type, sequence;

2. Check Object Status

-- Find all INVALID objects in a schema
SELECT
  owner,
  object_name,
  object_type,
  status,
  last_ddl_time,
  created
FROM dba_objects
WHERE status = 'INVALID'
  AND owner = 'SCHEMA_NAME'
ORDER BY object_type, object_name;

-- Summary of invalid objects by schema and type
SELECT
  owner,
  object_type,
  COUNT(*) AS invalid_count
FROM dba_objects
WHERE status = 'INVALID'
  AND owner NOT IN ('SYS', 'SYSTEM', 'PUBLIC', 'MDSYS', 'XDB')
GROUP BY owner, object_type
ORDER BY owner, invalid_count DESC;

-- Check if specific object is invalid
SELECT
  object_name,
  object_type,
  status,
  last_ddl_time,
  ROUND(SYSDATE - last_ddl_time, 2) AS days_since_ddl
FROM dba_objects
WHERE object_name = 'OBJECT_NAME'
  AND owner = 'SCHEMA_NAME';

3. Trace the Dependency Chain

-- Find what objects depend on a given object
SELECT
  d.name AS dependent_object,
  d.type AS dependent_type,
  d.referenced_name AS references_object,
  d.referenced_type,
  o.status
FROM dba_dependencies d
JOIN dba_objects o
  ON d.owner = o.owner
  AND d.name = o.object_name
  AND d.type = o.object_type
WHERE d.referenced_name = 'CHANGED_OBJECT_NAME'
  AND d.referenced_owner = 'SCHEMA_NAME'
ORDER BY d.type, d.name;

-- Find what a specific invalid object depends on
SELECT
  d.referenced_owner,
  d.referenced_name,
  d.referenced_type,
  o.status AS referenced_status
FROM dba_dependencies d
LEFT JOIN dba_objects o
  ON d.referenced_owner = o.owner
  AND d.referenced_name = o.object_name
  AND d.referenced_type = o.object_type
WHERE d.name = 'INVALID_OBJECT_NAME'
  AND d.owner = 'SCHEMA_NAME'
ORDER BY d.referenced_type, d.referenced_name;

-- Full dependency tree (hierarchical)
SELECT
  LPAD(' ', 2 * (LEVEL - 1)) || name AS dependency_tree,
  type,
  referenced_name,
  referenced_type,
  LEVEL
FROM dba_dependencies
WHERE owner = 'SCHEMA_NAME'
START WITH name = 'OBJECT_NAME'
CONNECT BY PRIOR referenced_name = name
  AND PRIOR referenced_owner = owner
  AND LEVEL <= 5;

4. Identify Recent DDL That Caused Invalidation

-- Recent DDL changes that may have triggered invalidation
SELECT
  owner,
  object_name,
  object_type,
  last_ddl_time,
  created
FROM dba_objects
WHERE last_ddl_time > SYSDATE - 1
  AND owner = 'SCHEMA_NAME'
ORDER BY last_ddl_time DESC;

-- Check for missing objects referenced by invalid objects
SELECT DISTINCT
  d.referenced_owner,
  d.referenced_name,
  d.referenced_type,
  'MISSING' AS status
FROM dba_dependencies d
WHERE d.owner = 'SCHEMA_NAME'
  AND d.name IN (
    SELECT object_name FROM dba_objects
    WHERE status = 'INVALID' AND owner = 'SCHEMA_NAME'
  )
  AND NOT EXISTS (
    SELECT 1 FROM dba_objects o
    WHERE o.owner = d.referenced_owner
      AND o.object_name = d.referenced_name
      AND o.object_type = d.referenced_type
  );

Resolution Steps

1. Recompile Individual Objects

-- Recompile a package specification
ALTER PACKAGE schema_name.package_name COMPILE;

-- Recompile a package body
ALTER PACKAGE schema_name.package_name COMPILE BODY;

-- Recompile a procedure
ALTER PROCEDURE schema_name.procedure_name COMPILE;

-- Recompile a function
ALTER FUNCTION schema_name.function_name COMPILE;

-- Recompile a view
ALTER VIEW schema_name.view_name COMPILE;

-- Recompile a trigger
ALTER TRIGGER schema_name.trigger_name COMPILE;

-- Recompile a type
ALTER TYPE schema_name.type_name COMPILE;

-- Recompile with debug information for troubleshooting
ALTER PROCEDURE schema_name.procedure_name COMPILE DEBUG;

-- Check errors after recompilation
SHOW ERRORS PROCEDURE schema_name.procedure_name;

2. Mass Recompilation with UTL_RECOMP

-- Recompile all invalid objects in the entire database
-- (Run as SYS, typically after patching or upgrade)
EXEC UTL_RECOMP.RECOMP_SERIAL();

-- Parallel recompilation (faster for many invalid objects)
-- Parameter = number of parallel threads (0 = auto-detect)
EXEC UTL_RECOMP.RECOMP_PARALLEL(4);

-- Recompile all invalid objects in a specific schema
EXEC UTL_RECOMP.RECOMP_SERIAL('SCHEMA_NAME');

-- Parallel recompilation for a specific schema
EXEC UTL_RECOMP.RECOMP_PARALLEL(4, 'SCHEMA_NAME');

3. Recompile Using the Supplied Script

-- Oracle provides a script to recompile all invalid objects
-- Run as SYS from SQL*Plus
@?/rdbms/admin/utlrp.sql

-- This script automatically uses parallel recompilation
-- when possible and reports results

4. Generate Recompilation Statements

-- Generate ALTER COMPILE statements for all invalid objects
SELECT
  'ALTER ' ||
  DECODE(object_type,
    'PACKAGE BODY', 'PACKAGE ' || owner || '.' || object_name || ' COMPILE BODY',
    object_type || ' ' || owner || '.' || object_name || ' COMPILE'
  ) || ';' AS recompile_statement
FROM dba_objects
WHERE status = 'INVALID'
  AND owner = 'SCHEMA_NAME'
ORDER BY
  DECODE(object_type,
    'TYPE', 1, 'TYPE BODY', 2,
    'PACKAGE', 3, 'PACKAGE BODY', 4,
    'FUNCTION', 5, 'PROCEDURE', 6,
    'VIEW', 7, 'TRIGGER', 8, 9
  );

5. Fix Underlying Errors

-- If errors reference missing columns, check the table structure
DESC schema_name.table_name;

-- If grants are missing, re-grant necessary privileges
GRANT SELECT ON schema_name.table_name TO other_schema;
GRANT EXECUTE ON schema_name.package_name TO other_schema;

-- If synonyms are broken, recreate them
CREATE OR REPLACE SYNONYM synonym_name FOR schema_name.object_name;

-- Verify fix by recompiling
ALTER PACKAGE schema_name.package_name COMPILE;
ALTER PACKAGE schema_name.package_name COMPILE BODY;

-- Confirm no errors remain
SELECT COUNT(*) AS remaining_errors
FROM dba_errors
WHERE owner = 'SCHEMA_NAME'
  AND name = 'OBJECT_NAME';

23ai Migration Considerations

Common Issues During Upgrade or CDB Migration

-- Find objects using deprecated features (pre-upgrade check)
-- Check for use of deprecated packages
SELECT DISTINCT
  d.owner,
  d.name,
  d.type,
  d.referenced_name AS deprecated_reference
FROM dba_dependencies d
WHERE d.referenced_name IN (
  'DBMS_STATS_INTERNAL',
  'DBMS_OBFUSCATION_TOOLKIT'  -- Deprecated in favor of DBMS_CRYPTO
)
AND d.owner NOT IN ('SYS', 'SYSTEM');

-- After CDB migration: check for objects referencing
-- non-existent common user objects
SELECT
  d.owner,
  d.name,
  d.type,
  d.referenced_owner,
  d.referenced_name
FROM dba_dependencies d
WHERE d.referenced_owner LIKE 'C##%'
  AND NOT EXISTS (
    SELECT 1 FROM dba_objects o
    WHERE o.owner = d.referenced_owner
      AND o.object_name = d.referenced_name
  );

-- Pre-upgrade: Run Oracle pre-upgrade tool to identify issues
-- $ORACLE_HOME/rdbms/admin/preupgrade.jar
-- Reviews all objects and flags potential incompatibilities

Post-Upgrade Recompilation

-- After upgrading to 23ai, always run:
@?/rdbms/admin/utlrp.sql

-- Verify all objects are valid after recompilation
SELECT owner, object_type, COUNT(*) AS invalid_count
FROM dba_objects
WHERE status = 'INVALID'
  AND owner NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
GROUP BY owner, object_type
ORDER BY owner, invalid_count DESC;

-- If objects remain invalid, check specific errors
SELECT owner, name, type, line, text
FROM dba_errors
WHERE owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, name, sequence;

Prevention Strategies

1. Dependency Tracking Before DDL

-- Before dropping or altering a table column, check dependencies
SELECT
  owner,
  name,
  type
FROM dba_dependencies
WHERE referenced_name = 'TABLE_TO_MODIFY'
  AND referenced_owner = 'SCHEMA_NAME'
  AND referenced_type = 'TABLE'
ORDER BY type, name;

-- Check column-level dependencies (11g+)
SELECT
  d.owner,
  d.name,
  d.type,
  d.referenced_name,
  LISTAGG(dc.column_name, ', ') WITHIN GROUP (ORDER BY dc.column_name) AS columns_used
FROM dba_dependencies d
LEFT JOIN dba_dependency_columns dc
  ON d.owner = dc.owner
  AND d.name = dc.name
  AND d.type = dc.type
WHERE d.referenced_name = 'TABLE_TO_MODIFY'
  AND d.referenced_owner = 'SCHEMA_NAME'
GROUP BY d.owner, d.name, d.type, d.referenced_name
ORDER BY d.type, d.name;

2. Pre-Upgrade Validation

-- Create a pre-upgrade validation report
SET SERVEROUTPUT ON
DECLARE
  v_invalid_count NUMBER;
BEGIN
  -- Count current invalid objects
  SELECT COUNT(*) INTO v_invalid_count
  FROM dba_objects
  WHERE status = 'INVALID'
    AND owner NOT IN ('SYS', 'SYSTEM', 'PUBLIC');

  DBMS_OUTPUT.PUT_LINE('Invalid objects before upgrade: ' || v_invalid_count);

  -- Attempt to recompile all invalid objects
  UTL_RECOMP.RECOMP_SERIAL();

  -- Recount after recompilation
  SELECT COUNT(*) INTO v_invalid_count
  FROM dba_objects
  WHERE status = 'INVALID'
    AND owner NOT IN ('SYS', 'SYSTEM', 'PUBLIC');

  DBMS_OUTPUT.PUT_LINE('Invalid objects after recompilation: ' || v_invalid_count);

  IF v_invalid_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: Resolve these before proceeding with upgrade');
  END IF;
END;
/

3. Post-Patching Recompilation Procedure

-- Standard post-patching procedure
-- Step 1: Record baseline
SELECT COUNT(*) AS invalid_before FROM dba_objects WHERE status = 'INVALID';

-- Step 2: Run recompilation
@?/rdbms/admin/utlrp.sql

-- Step 3: Verify results
SELECT COUNT(*) AS invalid_after FROM dba_objects WHERE status = 'INVALID';

-- Step 4: Investigate any remaining invalid objects
SELECT owner, object_name, object_type
FROM dba_objects
WHERE status = 'INVALID'
  AND owner NOT IN ('SYS', 'SYSTEM', 'PUBLIC', 'MDSYS', 'XDB')
ORDER BY owner, object_type, object_name;

4. Automated Invalid Object Monitoring

-- Create a scheduled job to detect and alert on invalid objects
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name   => 'CHECK_INVALID_OBJECTS',
    job_type   => 'PLSQL_BLOCK',
    job_action => '
      DECLARE
        v_count NUMBER;
      BEGIN
        SELECT COUNT(*) INTO v_count
        FROM dba_objects
        WHERE status = ''INVALID''
          AND owner NOT IN (''SYS'', ''SYSTEM'', ''PUBLIC'');

        IF v_count > 10 THEN
          -- Log or alert
          INSERT INTO dba_alert_log (alert_date, alert_type, alert_message)
          VALUES (SYSTIMESTAMP, ''INVALID_OBJECTS'',
                  v_count || '' invalid objects detected'');
          COMMIT;
        END IF;
      END;',
    repeat_interval => 'FREQ=HOURLY; INTERVAL=4',
    enabled    => TRUE
  );
END;
/

Related Errors

• ORA-00942 - Table or view does not exist (often the root cause)
• ORA-06508 - PL/SQL: could not find program unit being called
• ORA-06512 - PL/SQL error stack trace
• ORA-00904 - Invalid identifier (column renamed or dropped)
• ORA-01031 - Insufficient privileges (grant revoked)

Quick Reference

Emergency Response Steps

1. Check the exact object name and error details from the error message
2. Query DBA_ERRORS for compilation error text
3. Query DBA_DEPENDENCIES to trace the root cause
4. Fix the underlying issue (missing object, revoked grant, dropped column)
5. Recompile the object and verify with SHOW ERRORS
6. For mass invalidation, run @?/rdbms/admin/utlrp.sql or UTL_RECOMP.RECOMP_PARALLEL()

Quick Commands

-- Check object status
SELECT object_name, object_type, status FROM dba_objects
WHERE object_name = 'OBJECT_NAME' AND owner = 'SCHEMA_NAME';

-- View compilation errors
SELECT line, position, text FROM dba_errors
WHERE name = 'OBJECT_NAME' AND owner = 'SCHEMA_NAME' ORDER BY sequence;

-- Recompile single object
ALTER PACKAGE schema.pkg_name COMPILE;
ALTER PACKAGE schema.pkg_name COMPILE BODY;

-- Mass recompile a schema
EXEC UTL_RECOMP.RECOMP_SERIAL('SCHEMA_NAME');

-- Mass recompile entire database
@?/rdbms/admin/utlrp.sql

-- Count remaining invalid objects
SELECT owner, object_type, COUNT(*) FROM dba_objects
WHERE status = 'INVALID' GROUP BY owner, object_type ORDER BY 3 DESC;

Prevention Guidelines

• Check dependencies before DDL - Query DBA_DEPENDENCIES before dropping or altering objects
• Recompile after patching - Always run utlrp.sql after applying patches or upgrades
• Monitor invalid objects - Schedule regular checks for unexpected invalidations
• Use edition-based redefinition - For zero-downtime DDL changes in production
• Pre-validate upgrades - Run the Oracle pre-upgrade tool before major version changes
• Grant at schema level - Avoid granting on individual objects when possible to reduce breakage