Reasons for getting Invalid Objects
Invalid objects can and will occur for many reasons. You will usually find invalid objects
- after running (or failing to run) database preparation scripts,
- doing an export/import,
- upgrading, or
- applying patches.
Invalid objects are usually caused by
- missing grants,
- synonyms,
- views, tables or
- packages,
- but can also be caused by corrupted packages.
Reasons for compiling Invalid Objects:
Compiling invalid objects on your database is almost the equivalent of running scandisk on a PC hard drive. This should be one of the first things you check if you start experiencing problems with your Oracle database. It is also a good idea to schedule regular checks for invalid objects.
Where Can I get details about Invalid Objects:
The following query gives you quick count of the number of existing invalids
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS = ‘INVALID’;
The following query gives you detailed results
SELECT OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE STATUS = ‘INVALID’ GROUP BY OWNER, OBJECT_TYPE;
Run the following script to get a detailed listing of all invalid objects:
COLUMN OWNER FORMAT A16 HEADING ‘OWNER’
COLUMN OBJECT_NAME FORMAT A30 HEADING ‘OBJECT NAME’
COLUMN OBJECT_TYPE FORMAT A30 HEADING ‘OBJECT TYPE’
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE STATUS = ‘INVALID’
If you are looking for more specific queries such as by particular application object names or specific object types, modify your script as needed. The following two examples will find invalid PA objects or invalid package bodies:
COLUMN OWNER FORMAT A16 HEADING ‘OWNER’
COLUMN OBJECT_NAME FORMAT A30 HEADING ‘OBJECT NAME’
COLUMN OBJECT_TYPE FORMAT A16 HEADING ‘OBJECT TYPE’
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE STATUS = ‘INVALID’
AND OBJECT_NAME LIKE ‘PA%’
Recompile an individual object, connect to SQL*PLUS as the owner of the object and use one of the following depending on the object type
alter package <package_name> compile; (package specification)
alter package <package_name> compile body; (package body)
alter view <view_name> compile; (view)
You can use following command to get warnings/errors while manually compiling the invalid objects.
show errors
or
other method of finding as
select * from user_errors where name = ‘<OBJECT_NAME>’;
Another way to correct invalid objects is to run the adadmin utility as follows:
UNIX Specific:
1. Log in as APPS User : <applmgr username>/<applmgr password>
2. Start the adadmin-Utility from the Unix prompt with this command : The 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 Auto Install.
Click here to find more details on compiling invalid objects through ADADMIN utility.
WINDOWS Specific:
1. Log in as APPS User : <applmgr username>/<applmgr password>
b. Start the adadmin-Utility from the DOS prompt with this command:
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 followed:
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 <file> 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.
If recreating the package does not make the package valid, analyze the user_errors table to determine the cause of the invalid package :
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>’;