-------
Frequently Asked Questions about Invalid Objects.
Scope & Application
-------------------
This document is intended for any user who has a working knowledge of SQL and
the database that they are working with.
Contents
--------
1. Why do I have invalid objects? What causes them?
2. Why does Oracle Support always tell me to recompile my invalid objects?
3. Are invalid objects ever acceptable? How many is too many?
4. If we do not use the application with the invalid objects, can we delete
them?
5. Which OBJECTS table is best to use for queries? ALL, USER or DBA?
6. How can I get a quick count of my invalid objects (if any) for regular
maintenance?
7. I have invalid objects. How can I get a full or partial detailed list of
them?
8. How can I manually recompile individual invalid objects?
9. How can I use adadmin to recompile my invalid objects?
10. How can I recompile all my invalid objects using ADCOMPSC.pls?
11. What if I still have invalid objects than can not be resolved by
ADCOMPSC.pls?
Questions & Answers
-------------------
1. Why do I have invalid objects? What causes them?
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.
2. Why does Oracle Support always tell me to recompile my 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.
When you call in to Oracle Support with a database or installation issue,
one of the first questions they will probably ask is whether you have
checked for and resolved any invalid objects.
3. Are invalid objects ever acceptable? How many is too many?
If the invalid objects exist for a product or application that is not
installed, it may be ok to have some, but it is preferable to have no
invalid objects existing at all. If invalid objects exist for a product
or application that you do have installed and are using, then it should be
considered unacceptable and any existing invalid objects should be resolved
before further issues can occur.
There is no set number of invalid objects that could be considered
'acceptable' as each situation will vary widely from one database to the
next. You could just have a few invalid objects or they could number in
the hundreds or even thousands, but every effort should be made to resolve
them one way or another.
4. If we do not use the application with the invalid objects, can we delete
them?
There are times when invalid objects have occurred where you may opt to
simply delete them, but you must ensure that they are in a product or
application that is not used.
5. Which OBJECTS table is best to use for queries? ALL, USER or DBA?
You should normally use DBA_OBJECTS, but there may be occasions when you
will want to use USER_OBJECTS. It is not recommended to use ALL_OBJECTS.
USER_OBJECTS - returns objects owned by the user (schema) you are
connected as.
DBA_OBJECTS - returns every object in the Database.
6. How can I get a quick count of my invalid objects (if any) for regular
maintenance?
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;
7. I have invalid objects. How can I get a full or partial detailed list of
them?
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 A16 HEADING 'OBJECT TYPE'
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
ORDER BY OWNER, OBJECT_TYPE;
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%'
ORDER BY OBJECT_TYPE, OWNER;
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_TYPE LIKE 'PACKAGE BODY'
ORDER BY OWNER, OBJECT_NAME;
*** Note: For information on determining the source of invalid objects,
*** see Note 60558.1
8. How can I manually recompile individual invalid objects?
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 = '';
9. How can I use adadmin to recompile my invalid objects?
Another way to correct invalid objects is to run the adadmin utility as
follows:
* Set the Applications environment using the following steps:
1. Log in as applmgr (Applications file system owner).
2. Run the environment (UNIX) or command (Windows) file for the current
APPL_TOP and database.
UNIX:
The environment file is typically APPS.env, and is located
under APPL_TOP. From a Bourne, Korn, or Bash shell, type the following:
$ . APPS.env
Windows:
Run %APPL_TOP%\envshell.cmd using either Windows Explorer or the Run
command from the Start menu. This creates a Command Prompt window that
contains the required environment settings for Oracle Applications. Run
all subsequent commands in this Command Prompt window.
* Run command 'adadmin', and select the menu option for 'Compile/Reload
Applications Database Entities menu', and then the option to 'Compile
APPS schema'.
10. How can I recompile all my invalid objects using ADCOMPSC.pls?
NOTE: In later versions adcompsc.pls has been replaced with adcompsc.sql
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. If you encounter an ORA-1555 error
while running adcompsc.pls, just 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 you still have invalid objects, you should run
adcompsc.pls again. Keep running adcompsc.pls until number of invalid
objects stops decreasing.
11. What if I still have invalid objects than can not be resolved by
ADCOMPSC.pls?
If there are any objects still left INVALID, you can verify them by using
aderrchk.sql to record the remaining INVALID objects. Aderrchk.sql use the
same syntax as adcompsc.pls. This script is also supplied with the
Applications. You can 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 give you the sql that creates the packages. You can then
recreate the packages. SQL>@packageheader SQL>@packagebody
If recreating the package does not make the package valid you will have to
analyze the user_errors table to try to determine the cause of the invalid
package.
select text
from user_errors
where name = 'PACKAGENAME';
Miscellaneous Documentation
===========================
DOCUMENTATION
-------------
Note 60966.1 Getting Rid Of Those Pesky Invalid Objects
Note 60558.1 Determining the Source of Invalid Objects
USEFUL SCRIPTS
--------------
Note 73995.1 SCRIPT: RECOMPILING INVALID OBJECTS
Note 1019928.6 TFTS VERIFY STORED PROCEDURES
Extended Invalid Objects Scripts
--------------------------------
The following 4 scripts are provided for reference purposes only. They are not
supported by Oracle Support. Scripts written by Paul Ferguson 12/98
These scripts should be run from the APPS schema.
A. inval_objs.sql - List invalid objects
B. inval_vers.sql - List invalid objects + versions
C. inval_errors.sql - List invalid objects + errors
D. inval_depend.sql - List invalid objects + dependencies
A. inval_objs.sql
set feedback off
set verify off
set echo off
set pagesize 10000
column Name format A30 heading 'Object Name'
column Type format A16 heading 'Object Type'
column Stat format A8 heading 'Status'
break on none
spool inval_obj.lst
ttitle -
center 'Invalid objects' skip 2
SELECT object_name Name,
object_type Type,
status Stat
FROM user_objects
WHERE status != 'VALID'
ORDER BY object_name, object_type
/
ttitle off
set heading off
column total new_val tot_objects noprint
SELECT count(1) total
FROM user_objects
WHERE status != 'VALID'
/
SELECT 'There are ' || &tot_objects || ' invalid objects in
the ' || user || ' schema.'
FROM dual
/
set heading on
spool off
B. inval_vers.sql
set feedback off
set verify off
set echo off
set pagesize 10000
column Name format A28 heading 'Object Name'
column Type format A16 heading 'Object Type'
column Version format A30 heading 'Version'
break on none
spool inval_vers.lst
ttitle -
center 'Invalid objects - Versions' skip 2
SELECT uo.object_name Name,
uo.object_type Type,
substr(us.text, instr(us.text, '$Header')+9,
instr(substr(us.text, instr(us.text, '$Header')+9),
' ', 1, 3)-1) Version
FROM user_objects uo,
user_source us
WHERE uo.status != 'VALID'
AND us.name = uo.object_name
AND us.text like '%$Header%'
ORDER BY uo.object_name
/
spool off
C. inval_errors.sql
set feedback off
set verify off
set echo off
set pagesize 10000
column Name format A20 heading 'Object Name'
column Type format A16 heading 'Object Type'
column Err format A30 heading 'Errors'
column Lineno format 999999 heading 'Line #'
break on Name skip 2 on Type
spool inval_errors.lst
ttitle -
center 'Invalid objects - Errors' skip 2
SELECT uo.object_name Name,
uo.object_type Type,
ue.line Lineno,
ue.text Err
FROM user_objects uo,
user_errors ue
WHERE uo.status != 'VALID'
AND ue.name = uo.object_name
ORDER BY uo.object_name, uo.object_type, ue.line
/
spool off
D. inval_depend.sql
set feedback off
set verify off
set echo off
set pagesize 10000
column object_name format A18 heading "Object name"
column object_type format A12 heading "Type"
column referenced_name format A18 heading "Depends on:"
column referenced_owner format A12 heading "Owner"
column referenced_type format A12 heading "Type"
break on object_name skip 2 on object_type
spool inval_depend.lst
ttitle -
center 'Invalid objects - Dependencies' skip 2
SELECT object_name,
object_type,
referenced_owner,
referenced_type,
referenced_name
FROM user_objects,
user_dependencies
WHERE object_name = name
AND status != 'VALID'
ORDER BY object_name, object_type, referenced_owner,
referenced_type, referenced_name
/
spool off
Frequently Asked Questions about Invalid Objects.
Scope & Application
-------------------
This document is intended for any user who has a working knowledge of SQL and
the database that they are working with.
Contents
--------
1. Why do I have invalid objects? What causes them?
2. Why does Oracle Support always tell me to recompile my invalid objects?
3. Are invalid objects ever acceptable? How many is too many?
4. If we do not use the application with the invalid objects, can we delete
them?
5. Which OBJECTS table is best to use for queries? ALL, USER or DBA?
6. How can I get a quick count of my invalid objects (if any) for regular
maintenance?
7. I have invalid objects. How can I get a full or partial detailed list of
them?
8. How can I manually recompile individual invalid objects?
9. How can I use adadmin to recompile my invalid objects?
10. How can I recompile all my invalid objects using ADCOMPSC.pls?
11. What if I still have invalid objects than can not be resolved by
ADCOMPSC.pls?
Questions & Answers
-------------------
1. Why do I have invalid objects? What causes them?
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.
2. Why does Oracle Support always tell me to recompile my 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.
When you call in to Oracle Support with a database or installation issue,
one of the first questions they will probably ask is whether you have
checked for and resolved any invalid objects.
3. Are invalid objects ever acceptable? How many is too many?
If the invalid objects exist for a product or application that is not
installed, it may be ok to have some, but it is preferable to have no
invalid objects existing at all. If invalid objects exist for a product
or application that you do have installed and are using, then it should be
considered unacceptable and any existing invalid objects should be resolved
before further issues can occur.
There is no set number of invalid objects that could be considered
'acceptable' as each situation will vary widely from one database to the
next. You could just have a few invalid objects or they could number in
the hundreds or even thousands, but every effort should be made to resolve
them one way or another.
4. If we do not use the application with the invalid objects, can we delete
them?
There are times when invalid objects have occurred where you may opt to
simply delete them, but you must ensure that they are in a product or
application that is not used.
5. Which OBJECTS table is best to use for queries? ALL, USER or DBA?
You should normally use DBA_OBJECTS, but there may be occasions when you
will want to use USER_OBJECTS. It is not recommended to use ALL_OBJECTS.
USER_OBJECTS - returns objects owned by the user (schema) you are
connected as.
DBA_OBJECTS - returns every object in the Database.
6. How can I get a quick count of my invalid objects (if any) for regular
maintenance?
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;
7. I have invalid objects. How can I get a full or partial detailed list of
them?
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 A16 HEADING 'OBJECT TYPE'
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
ORDER BY OWNER, OBJECT_TYPE;
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%'
ORDER BY OBJECT_TYPE, OWNER;
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_TYPE LIKE 'PACKAGE BODY'
ORDER BY OWNER, OBJECT_NAME;
*** Note: For information on determining the source of invalid objects,
*** see Note 60558.1
8. How can I manually recompile individual invalid objects?
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 = '';
9. How can I use adadmin to recompile my invalid objects?
Another way to correct invalid objects is to run the adadmin utility as
follows:
* Set the Applications environment using the following steps:
1. Log in as applmgr (Applications file system owner).
2. Run the environment (UNIX) or command (Windows) file for the current
APPL_TOP and database.
UNIX:
The environment file is typically APPS.env, and is located
under APPL_TOP. From a Bourne, Korn, or Bash shell, type the following:
$ . APPS.env
Windows:
Run %APPL_TOP%\envshell.cmd using either Windows Explorer or the Run
command from the Start menu. This creates a Command Prompt window that
contains the required environment settings for Oracle Applications. Run
all subsequent commands in this Command Prompt window.
* Run command 'adadmin', and select the menu option for 'Compile/Reload
Applications Database Entities menu', and then the option to 'Compile
APPS schema'.
10. How can I recompile all my invalid objects using ADCOMPSC.pls?
NOTE: In later versions adcompsc.pls has been replaced with adcompsc.sql
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. If you encounter an ORA-1555 error
while running adcompsc.pls, just 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 you still have invalid objects, you should run
adcompsc.pls again. Keep running adcompsc.pls until number of invalid
objects stops decreasing.
11. What if I still have invalid objects than can not be resolved by
ADCOMPSC.pls?
If there are any objects still left INVALID, you can verify them by using
aderrchk.sql to record the remaining INVALID objects. Aderrchk.sql use the
same syntax as adcompsc.pls. This script is also supplied with the
Applications. You can 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 give you the sql that creates the packages. You can then
recreate the packages. SQL>@packageheader SQL>@packagebody
If recreating the package does not make the package valid you will have to
analyze the user_errors table to try to determine the cause of the invalid
package.
select text
from user_errors
where name = 'PACKAGENAME';
Miscellaneous Documentation
===========================
DOCUMENTATION
-------------
Note 60966.1 Getting Rid Of Those Pesky Invalid Objects
Note 60558.1 Determining the Source of Invalid Objects
USEFUL SCRIPTS
--------------
Note 73995.1 SCRIPT: RECOMPILING INVALID OBJECTS
Note 1019928.6 TFTS VERIFY STORED PROCEDURES
Extended Invalid Objects Scripts
--------------------------------
The following 4 scripts are provided for reference purposes only. They are not
supported by Oracle Support. Scripts written by Paul Ferguson 12/98
These scripts should be run from the APPS schema.
A. inval_objs.sql - List invalid objects
B. inval_vers.sql - List invalid objects + versions
C. inval_errors.sql - List invalid objects + errors
D. inval_depend.sql - List invalid objects + dependencies
A. inval_objs.sql
set feedback off
set verify off
set echo off
set pagesize 10000
column Name format A30 heading 'Object Name'
column Type format A16 heading 'Object Type'
column Stat format A8 heading 'Status'
break on none
spool inval_obj.lst
ttitle -
center 'Invalid objects' skip 2
SELECT object_name Name,
object_type Type,
status Stat
FROM user_objects
WHERE status != 'VALID'
ORDER BY object_name, object_type
/
ttitle off
set heading off
column total new_val tot_objects noprint
SELECT count(1) total
FROM user_objects
WHERE status != 'VALID'
/
SELECT 'There are ' || &tot_objects || ' invalid objects in
the ' || user || ' schema.'
FROM dual
/
set heading on
spool off
B. inval_vers.sql
set feedback off
set verify off
set echo off
set pagesize 10000
column Name format A28 heading 'Object Name'
column Type format A16 heading 'Object Type'
column Version format A30 heading 'Version'
break on none
spool inval_vers.lst
ttitle -
center 'Invalid objects - Versions' skip 2
SELECT uo.object_name Name,
uo.object_type Type,
substr(us.text, instr(us.text, '$Header')+9,
instr(substr(us.text, instr(us.text, '$Header')+9),
' ', 1, 3)-1) Version
FROM user_objects uo,
user_source us
WHERE uo.status != 'VALID'
AND us.name = uo.object_name
AND us.text like '%$Header%'
ORDER BY uo.object_name
/
spool off
C. inval_errors.sql
set feedback off
set verify off
set echo off
set pagesize 10000
column Name format A20 heading 'Object Name'
column Type format A16 heading 'Object Type'
column Err format A30 heading 'Errors'
column Lineno format 999999 heading 'Line #'
break on Name skip 2 on Type
spool inval_errors.lst
ttitle -
center 'Invalid objects - Errors' skip 2
SELECT uo.object_name Name,
uo.object_type Type,
ue.line Lineno,
ue.text Err
FROM user_objects uo,
user_errors ue
WHERE uo.status != 'VALID'
AND ue.name = uo.object_name
ORDER BY uo.object_name, uo.object_type, ue.line
/
spool off
D. inval_depend.sql
set feedback off
set verify off
set echo off
set pagesize 10000
column object_name format A18 heading "Object name"
column object_type format A12 heading "Type"
column referenced_name format A18 heading "Depends on:"
column referenced_owner format A12 heading "Owner"
column referenced_type format A12 heading "Type"
break on object_name skip 2 on object_type
spool inval_depend.lst
ttitle -
center 'Invalid objects - Dependencies' skip 2
SELECT object_name,
object_type,
referenced_owner,
referenced_type,
referenced_name
FROM user_objects,
user_dependencies
WHERE object_name = name
AND status != 'VALID'
ORDER BY object_name, object_type, referenced_owner,
referenced_type, referenced_name
/
spool off
