Recompiling Invalid Schema Objects

Objects in a database tend to get invalid due to operation such as patches, DDL changes and upgrades etc. The invalid objects are to compiled to ensure proper health of the database and user's access to them. I will provide here the ways you can compile objects in Oracle.

Identifying Invalid Objects:

col format OBJECT_NAME a40

SQL> SELECT OBJECT_NAME,
                            OBJECT_TYPE,
                            OWNER,
                            STATUS
           FROM DBA_OBJECTS
           WHERE STATUS='INVALID'
           ORDER BY OBJECT_NAME, OBJECT_TYPE, OWNER;

to get invalid objects list for a particular schema below is the SQL:

col format OBJECT_NAME a40

SQL> SELECT OBJECT_NAME,
                          OBJECT_TYPE,
                          OWNER,
                          STATUS
           FROM DBA_OBJECTS
          WHERE OWNER='DEPT' AND STATUS='INVALID'
          ORDER BY OBJECT_NAME, OBJECT_TYPE, OWNER;

DBMS_UTILITY.COMPILE_SCHEMA:
DBMS_UTILITY package proides COMPILE_SCHEMA procedure to compile all the objects in a schema:

SQL> EXECUTE DBMS_UTILITY.COMPILE_SCHEMA ('SCHEMA_NAME');

UTLRP and UTLPRP:
UTLRP and UTLPRP are the oracle provided scripts to compile all invalid objects in oracle database.

Ex: SQL> ?/rdbms/admin/utlrp

Manual Approach:
Invalid Objects can be compiled individually after you have the list of invalid objects in your oracle database. Below are some of the examples:

SQL> ALTER PACKAGE package_name COMPILE;

SQL> ALTER PACKAGE package_name COMPILE BODY;

SQL> ALTER PROCEDURE procedure_name COMPILE;

SQL> ALTER FUNCTION function_name COMPILE;

SQL> ALTER TRIGGER trigger_name COMPILE;

SQL> ALTER VIEW view_name COMPILE;


An alternative approach is to use the DBMS_DDL package to perform the recompilations:

SQL> EXEC DBMS_DDL.alter_compile('PACKAGE', 'SCHEMA_NAME', 'PACKAGE_NAME');

SQL> EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'SCHEMA_NAME', 'PACKAGE_NAME');

SQL> EXEC DBMS_DDL.alter_compile('PROCEDURE', 'SCHEMA_NAME', 'PROCEDURE_NAME');

SQL> EXEC DBMS_DDL.alter_compile('FUNCTION', 'SCHEMA_NAME', 'FUNCTION_NAME');

SQL> EXEC DBMS_DDL.alter_compile('TRIGGER', 'SCHEMA_NAME', 'TRIGGER_NAME');
 
In addition to the above approaches you can write your own script to get the invalid objects and recompile them using the manual approach to recompile them all included in the script.