How to Recompile Invalid Schema Objects in Oracle

Operations such as upgrades, patches, and DDL changes can invalidate schema objects. While Oracle provides automatic recompilation on demand, this process can be time-consuming and may not address complex dependencies efficiently. Proactively recompiling invalid objects can reduce runtime delays and help identify any changes that may have caused issues. This guide outlines various methods to recompile invalid schema objects.

Identifying Invalid Objects

Before recompiling, you must identify invalid objects in the database. Use the following query on the DBA_OBJECTS view to locate invalid objects:

-- Query 1: Check all invalid objects in the database
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 Query to Check Invalid Objects with Additional Details
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;


-- Query 2: Check invalid objects for a specific schema
COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
AND    owner = 'SCOTT'
ORDER BY object_type, object_name;

-- Query 3: Check invalid objects with a specific name
COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
AND    object_name = 'MY_OBJECT'
ORDER BY owner, object_type;

-- Query 4: Check invalid objects of a specific type
COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
AND    object_type = 'PACKAGE'
ORDER BY owner, object_name;

-- Query 5: Check invalid objects with multiple criteria
COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
AND    owner = 'SCOTT'
AND    object_type = 'FUNCTION'
ORDER BY object_name;

-- Query 6: Check invalid objects excluding specific types
COLUMN object_name FORMAT A30
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;

-- Query 7: Check invalid objects created after a specific date
COLUMN object_name FORMAT A30
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;

This query provides a list of invalid objects, helping you decide the most suitable recompilation method.

Methods for Recompiling Invalid Objects

1. Manual Recompilation

For a small number of invalid objects, manual recompilation might suffice. Use the ALTER command for various object types:

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;

Alternatively, use the DBMS_DDL package for PL/SQL objects:

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');

2. Using the UTL_RECOMP Package

The UTL_RECOMP package provides procedures for serial and parallel recompilation of invalid objects:

• <strong>RECOMP_SERIAL</strong>: Recompiles objects serially, one at a time.
• <strong>RECOMP_PARALLEL</strong>: Recompiles objects in parallel using multiple threads.

Procedure Definitions

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);

Parameters

• <strong>schema</strong>: The schema whose invalid objects are recompiled. If NULL, all invalid objects in the database are recompiled.
• <strong>threads</strong>: Number of threads for parallel execution. Defaults to the job_queue_processes parameter value.
• <strong>flags</strong>: Reserved for internal diagnostics.

Examples

Schema-level recompilation:

EXEC UTL_RECOMP.recomp_serial('W3BUDDY');
EXEC UTL_RECOMP.recomp_parallel(4, 'W3BUDDY');

Database-level recompilation:

EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

Restrictions:

• Parallel execution uses the job queue. Existing jobs are disabled during recompilation.
• Must be run as SYS or a user with SYSDBA privileges.
• Avoid concurrent DDL operations to prevent deadlocks.

3. Using utlrp.sql and utlprp.sql Scripts

Oracle provides these scripts for database-wide recompilation of invalid objects. Located in $ORACLE_HOME/rdbms/admin, they are wrappers around the UTL_RECOMP package.

• <strong>utlrp.sql</strong>: Calls utlprp.sql with a parameter of 0.
• <strong>utlprp.sql</strong>: Accepts a single parameter for parallelism:
  • 0: Parallelism derived from CPU_COUNT.
  • 1: Serial recompilation.
  • N: Recompilation using N threads.

Example

SQL> @?/rdbms/admin/utlprp.sql 0

Run these scripts as SYS or a user with SYSDBA privileges.

4. Using DBMS_UTILITY.compile_schema

The DBMS_UTILITY.compile_schema procedure recompiles all invalid objects in a specified schema.

Example

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT', compile_all => false);

• <strong>compile_all</strong>: If true, all objects are compiled, regardless of validity. Default is false.

Conclusion

• Use manual recompilation or custom scripts for smaller tasks.
• Leverage UTL_RECOMP or Oracle-provided scripts (utlrp.sql and utlprp.sql) for database-wide recompilation.
• Use DBMS_UTILITY.compile_schema for schema-specific recompilation.

Proactively recompiling invalid objects minimizes runtime errors, ensures code consistency, and reduces the impact of complex dependencies.