How To Recompile Invalid Objects in Oracle

Recompiling Invalid Schema Objects

The DBA_OBJECTS view can be used to identify invalid objects using the following query:


SQL> COLUMN object_name FORMAT A30
SQL> SELECT substr(owner,1,18) owner, object_type, substr(object_name,1,30) object_name, status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

There are FOUR ways to recompile invalid objects in schema.

1. DBMS_DDL
2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL

1-      DBMS_DDL.ALTER_COMPILE

Syntax
SQL> EXEC dbms_ddl.alter_compile ( type , schema, name);

Example
SQL> EXEC dbms_ddl.alter_compile ('PROCEDURE' , 'JOHN ' , 'TEST');

2-      DBMS_UTILITY.COMPILE_SCHEMA

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema.

Example
SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'JOHN');

3-      UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects.

The RECOMP_SERIAL procedure recompiles all the invalid objects one at a time.

The RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads.  

Example :

-- Schema level.
SQL> EXEC UTL_RECOMP.recomp_serial('JOHN');
SQL> EXEC UTL_RECOMP.recomp_parallel(4, 'JOHN');

-- Database level.
SQL> EXEC UTL_RECOMP.recomp_serial();
SQL> EXEC UTL_RECOMP.recomp_parallel(4);

Restrictions associated with the use of this package :

• Parallel execution is performed using the job queue. All existing jobs are marked as disabled until the operation is complete.
• The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA privileges.
• Running DDL operations at the same time as this package may result in deadlocks.

4-     utlrp.sql and utlprp.sql  - - SCRIPTS

·         The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database.

·         They are typically run after major database changes such as upgrades or patches.

·         Located in the $ORACLE_HOME/rdbms/admin directory.

·         The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0".

0 - The level of parallelism is derived based on the CPU_COUNT parameter.

·         The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:

Note : Both scripts must be run as the SYS user or another user with SYSDBA privilege , to work correctly.