To get a quick count of the number of existing invalids (if any), use the
following select statement:
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). Use one of the following depending on the object
type:
SQL> alter package compile; (package
specification)
SQL> alter package compile body; (package
body)
SQL> alter view compile; (view)
If the object compiles with warnings, use either of the following to see
the errors that caused the warnings:
SQL> show errors
or
SQL> select * from user_errors where name = '';
Another way to correct invalid objects is to run the adadmin utility as
follows:
UNIX OPERATING PLATFORM
-----------------------
a. Log in as applmgr: /
b. Start the utility from the Unix prompt with this command:
$ adadmin
The utility will then ask you a series of questions.
c. 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 the same parallel phases as AutoInstall.
c. Under the Maintain Applications Database Objects Menu,
select Compile APPS schema(s)
=================
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 follows:
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 derrchk.
sql to record the remaining INVALID objects. Aderrchk.sql use 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 will not compile, try the following:
select text
from user_source
where name = 'OBJECTNAME'
and text like '%Header%';
This script will provide the sql that creates the packages > recreate
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 = 'PACKAGENAME';
=================
For most the application there is a script for compiling invalid objects and nice report on pre and post invalid objects
for example $CZ_TOP/patch/115/sql
the scrept czclnup11.sql
output will looks like
Objects that are still invalid:
-------------------------------
PACKAGE BODY : CZ_IMP_SINGLE_DEBUG
PACKAGE BODY : CZ_RUNTIME
PACKAGE BODY : CZ_UIOA_PVT
===================
Metalink DOC
Step by Step Troubleshooting Guide to Solve APPS Invalid Objects
113947.1Resolving Invalid Objects in Oracle Applications
74660.1
