Monday, June 8, 2009

How to compile the invalid objects...?

Applying Patches can create invalid objects.
To get a quick count of the number of existing invalids (if any),use the following select statement :
SELECT COUNT(*)FROM DBA_OBJECTSWHERE STATUS = 'INVALID';
For a more detailed query, use the following script :
SELECT OWNER, OBJECT_TYPE, COUNT(*)FROM DBA_OBJECTSWHERE STATUS = 'INVALID'GROUP BY OWNER, OBJECT_TYPE;

To recompile an individual object, connect to SQL*PLUS as the owner of the object (generally apps) and useone of the following depending on the object type
alter package compile; (package specification)alter package compile body; (package body)alter view compile; (view)If the object compiles with warnings, use either of the following to see the errors that caused the warnings :show errorsORselect * from user_errors where name = '';

Another way to correct invalid objects is to run the adadmin utility as follows:UNIX OPERATING PLATFORM1. Log in as APPS User : /

2. Start the adadmin-Utility from the Unix prompt with this command :adadminThe utility will then ask you a series of questions.

3. Under the Maintain Applications Database Objects Menu, select Compile APPS schema(s)This task spawns parallel workers to compile invalid database objects in your APPS schema(s). It uses thesame parallel phases as AutoInstall.Also try running

$ORACLE_HOME/rdbms/admin/utlrp.sql ( as sysdba )Applying Patches can create invalid objects.

To get a quick count of the number of existing invalids (if any),use the following select statement :

SELECT COUNT(*)FROM DBA_OBJECTSWHERE STATUS = 'INVALID';

For a more detailed query, use the following script :

SELECT OWNER, OBJECT_TYPE, COUNT(*)FROM DBA_OBJECTSWHERE STATUS = 'INVALID'GROUP BY OWNER, OBJECT_TYPE;

To recompile an individual object, connect to SQL*PLUS as the owner of the object (generally apps) and useone of the following depending on the object type :

alter package compile; (package specification)alter package compile body; (package body)alter view compile; (view)If the object compiles with warnings, use either of the following to see the errors that caused the warnings :show errorsORselect * from user_errors where name = '';


Another way to correct invalid objects is to run the adadmin utility as follows:UNIX OPERATING PLATFORM1. Log in as APPS User : /


2. Start the adadmin-Utility from the Unix prompt with this command :adadminThe utility will then ask you a series of questions.


3. Under the Maintain Applications Database Objects Menu, select Compile APPS schema(s)This task spawns parallel workers to compile invalid database objects in your APPS schema(s).


It uses thesame parallel phases as AutoInstall.Also try running $ORACLE_HOME/rdbms/admin/utlrp.sql ( as sysdba )

No comments:

Post a Comment