We get invalid objects in oracle database, if a dependent object is changed.



We try to understand from an example, suppose we rebuild a table (EMP), the indexes of that table (EMP) will become invalid because indexes use the tables old row ids and after rebuilding the row ids of table (EMP) has been changed.



It is the same with objects like packages, procedures and functions.



If the invalid objects exist, it should be unacceptable. Make sure that we have no invalid object

existing in our database.



If we find invalid objects, we may opt to simply delete them, but we must ensure that is not used.



How to find invalid objects?



We use following query: (this will show count of all invalid objects)



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


We use another query for more details about invalid objects:


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


If above query return rows, we use another query for detailed 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 above query return invalid object, we must compile invalid object. Three methods to compilation of invalid objects:



Method 1: Manually recompile invalid objects.



Use following SQL:



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 = '';



Method 2: Use adadmin utility to recompile invalid objects

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, run Task 7, which is 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.


WINDOWS NT OPERATING PLATFORM


a. Log in as applmgr: /
b. Start the utility from the Dos prompt with this command: > adadmin
c. Under the Maintain Applications Database Objects Menu, run Task 5 which is Compile APPS schema(s)



Method 3: Recompile all invalid objects using ADCOMPSC.pls



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


The script can be run as follows:


cd $AD_TOP/sql
$ sqlplus /nolog
SQL>@adcompsc.sql


Enter value for 1:

Enter value for 2: Connected.

Enter value for 3:

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.23


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



Troubleshooting:



If there are any objects still left INVALID, you can verify them by using aderrchk.sql to record the remaining INVALID objects.



The script can be run as follows:


cd $AD_TOP/sql
$ sqlplus /nolog
SQL>@aderrchk.sql



Enter value for 1:

Enter value for 2: Connected.

Enter value for 3:

PL/SQL procedure successfully completed.

Enter value for 4:

schema name 'apps'

schema password *****

objects starting with 'FLEX_CENTRAL_PUR_CON_V'

spool file 'anuperror.lst'

Enter value for 5:
5 - Error behavior

FAILINV - Fail if any invalid objects found at all
FAILERR - Fail if any invalid objects found outside user_errors

NOFAIL - Do not fail, period.



Find the dependents?



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
/