Invalid Objects and Compilation

Recompiling Invalid Objects:

Schema objects can be invalidated by various operations such as upgrades, patches and DDL changes. These changes will not cause compilation failures but will revalidate by automatic recompilation. This can take long time to complete, For this reason it makes sense to compile invalid objects in advance of user calls.

For compiling invalid object we have various methods:

1. Using utlrp.sql at $ORACLE_HOME/RDBMS/ADMIN
2.  Using ADADMIN utility
3. Manually compile

It is always a good idea to know the count of invalid objects before we compile it and again verify after compilation.

We can get count of invalid objects from dba_objects table as below:

SQL> select count(*) from dba_objects where status='INVALID';

UTLRP.SQL:

we can execute utlrp.sql from $ORACLE_HOME/RDBMS/ADMIN and this script will call utlprp.sql from the same location and will compile invalid objects.

ADADMIN Utility:

We can also compile the invalid objects with adadmin utility with below steps:

• Login as application tier user (APPLMGR in my case)
• Set environment variable (under APPL_TOP/APPS[sid]_[hostname].env)
• adadmin  and Answer the list of question and will get the adadmin menu
• option 3 compile/reload Applications Database Entities menu
• option 1 Compile Apps Schema

Through adadmin utility adcompsc.pls script is called and it will compile all schema in order of sys,system and apps etc. This script is located at  $AD_TOP/sql

We can also compile single schema with adcompsc.pls as below:

sqlplus @adcompsc.pls apps [apps_password]

Manual Compilation of Invalid Objects:

We can compile any package, procedure, function, trigger and view etc. as given below:

SQL>ALTER PACKAGE my_package COMPILE;

SQL>ALTER PACKAGE my_package COMPILE BODY;

SQL>ALTER PROCEDURE my_procedure COMPILE;

SQL>ALTER FUNCTION my_function COMPILE;

SQL>ALTER TRIGGER my_trigger COMPILE;

SQL>ALTER VIEW my_view COMPILE;