Recompile Invalid Schema Objects in Oracle

Invalid schema objects can cause runtime errors and unexpected behavior—especially after upgrades, patching, or DDL changes. Oracle can recompile them automatically, but it’s often better to handle them proactively. This guide walks you through identifying and recompiling invalid objects using manual methods, Oracle packages, and built-in scripts.

Identify Invalid Objects

Before recompilation, identify invalid objects using these diagnostic queries.

All Invalid Objects in the Database

-- Simple View
COLUMN object_name FORMAT A30
SELECT owner, object_type, object_name, status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

-- Enhanced View with Timestamps
SET LINESIZE 150
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A20
COLUMN status FORMAT A10
COLUMN created FORMAT A20
COLUMN last_ddl_time FORMAT A20

SELECT owner,
       object_type,
       object_name,
       status,
       TO_CHAR(created, 'YYYY-MM-DD HH24:MI:SS') AS created,
       TO_CHAR(last_ddl_time, 'YYYY-MM-DD HH24:MI:SS') AS last_ddl_time
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

Filtered Queries

-- For a specific schema
SELECT owner, object_type, object_name, status
FROM   dba_objects
WHERE  status = 'INVALID' AND owner = 'SCOTT'
ORDER BY object_type, object_name;

-- For a specific object name
SELECT owner, object_type, object_name, status
FROM   dba_objects
WHERE  status = 'INVALID' AND object_name = 'MY_OBJECT'
ORDER BY owner, object_type;

-- For a specific object type
SELECT owner, object_type, object_name, status
FROM   dba_objects
WHERE  status = 'INVALID' AND object_type = 'PACKAGE'
ORDER BY owner, object_name;

-- With multiple filters (e.g. schema + type)
SELECT owner, object_type, object_name, status
FROM   dba_objects
WHERE  status = 'INVALID'
AND    owner = 'SCOTT'
AND    object_type = 'FUNCTION'
ORDER BY object_name;

-- Exclude object types (e.g. VIEW, SYNONYM)
SELECT owner, object_type, object_name, status
FROM   dba_objects
WHERE  status = 'INVALID'
AND    object_type NOT IN ('VIEW', 'SYNONYM')
ORDER BY owner, object_type, object_name;

-- Created after a certain date
SELECT owner, object_type, object_name, status, created
FROM   dba_objects
WHERE  status = 'INVALID'
AND    created > TO_DATE('2024-01-01', 'YYYY-MM-DD')
ORDER BY created DESC;

Method 1: Manual Recompilation

Best for small numbers of invalid objects.

-- Basic ALTER commands
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

-- Using DBMS_DDL
EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');

Method 2: Using UTL_RECOMP Package

Oracle’s built-in package for serial and parallel recompilation of invalid objects.

Examples

-- Recompile One Schema (Serial)
EXEC UTL_RECOMP.recomp_serial('HR');

-- Recompile One Schema (Parallel)
EXEC UTL_RECOMP.recomp_parallel(4, 'HR');

-- Recompile All Schemas (Serial)
EXEC UTL_RECOMP.recomp_serial();

-- Recompile All Schemas (Parallel)
EXEC UTL_RECOMP.recomp_parallel(4);

Reference

PROCEDURE RECOMP_SERIAL(
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);

PROCEDURE RECOMP_PARALLEL(
   threads  IN   PLS_INTEGER DEFAULT NULL,
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);

📌 Notes:

• Requires SYS or SYSDBA privileges.
• Avoid concurrent DDL during recompilation.
• Uses job queue (disables jobs temporarily).

📜 Method 3: Using utlrp.sql or utlprp.sql Scripts

Oracle-provided SQL scripts in $ORACLE_HOME/rdbms/admin directory.

-- Recompile all invalid objects (parallelism = auto)
@?/rdbms/admin/utlprp.sql 0

🗒️ utlrp.sql simply wraps utlprp.sql with a default parameter of 0.

Parallelism Options:

• 0: Based on CPU_COUNT
• 1: Serial
• N: N threads (e.g., 4)

📌 Must be run as SYS or a SYSDBA user.

Method 4: DBMS_UTILITY.compile_schema

Recompile all invalid objects within a single schema.

-- Only invalid objects
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT', compile_all => false);

-- Recompile everything (valid and invalid)
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT', compile_all => true);

Conclusion

Proactively recompiling invalid objects ensures performance, reduces runtime errors, and helps pinpoint issues after structural changes.