Tag: EBS

SELECT COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID';

For a more detailed query, use the following script :

SELECT OWNER, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS
WHERE 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 use one 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 errors

OR

select * from user_errors where name = '';

Another way to correct invalid objects is to run the adadmin utility as follows:

1. Log in as APPS User : /

2. Start the adadmin-Utility from the Unix prompt with this command :

adadmin

The utility will then ask you a series of questions.

3. Under the Maintain Applications Database Objects Menu, select Compile APPS schema(s)

Other Way :
 
Also try running $ORACLE_HOME/rdbms/admin/utlrp.sql ( as sysdba )

Within Applications, there is a script to compile INVALID objects – called ADCOMPSC.pls

 Arguments for ADCOMPSC.pls :

1 – Schema to run in
2 – Password for schema
3 – Check errors for objects starting with #3

NOTE: The order in which to compile Invalid Objects in schemas is SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL should exist in all schema’s. In case of an ORA-1555 error while running adcompsc.pls, restart the script.

The script can be run as followed :

cd $AD_TOP/sql
sqlplus @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD %

Example : SQL> @adcompsc.pls apps apps %

After the script completes, check for invalid objects again. If the number has decreased, but invalid objects still exist, run adcompsc.pls again. Keep running adcompsc.pls until number of invalid objects stops decreasing.

If there are any objects still left INVALID, verify them by using the script ‘aderrchk.sql’ to record the remaining INVALID objects. ‘Aderrchk.sql’ uses the same syntax as ‘adcompsc.pls’. This script is also supplied with the Applications. Send the aderrchk.sql to a file using the spool command in sqlplus.

e.g. sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD %

For objects which will not compile, try the following :

select text
from user_source
where name = 'OBJECTNAME'
and text like '%Header%';

This script will provide the script that creates the packages/recreates the packages.

SQL>@packageheader
SQL>@packagebody

If recreating the package does not make the package valid, analyze the user_errors table to determine the cause of the invalid package :

select text
from user_errors
where name = '';

Thank You
Osama Mustafa