Invalid objects in the E-Business Suite Environment

Invalid objects in the E-Business Suite Environment

Invalid objects will appear for several reasons and it is not very easy to identify invalid objects as the root cause of a failure. In many cases invalid objects can be identified for failures reported from the functional area or for any misbehavior identified. In any case you can expect invalid objects, when you have imported a Database or when you have applied a Patch bringing new PL/SQL Packages. If you are using adpatch with the Standard options, a compilation of invalid objects for the APPS Schema is included by default - many DBA's disable this compilation via "adpatch options=nocompile", to save time. In any case, it is a good idea to check the E-Business Suite Instance on a regular basis for invalid objects.

How many invalid objects are in the Database ?

select owner,object_type,COUNT(*)
from dba_objects
where status='INVALID'
group by owner, object_type;

Check for details about the invalid objects :

column owner format A9
column object_name format A31
column object_type format A15
column last_ddl_time format A10
spool invalids.lst
select OWNER, OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME
from dba_objects where status='INVALID'
order by owner;

 

Validating invalid objects

Manual compilation of invalid objects:

alter package <owner>.<name of the package> compile;
alter package <owner>.<name of the package> compile body;
alter view <owner>.<name of the view> compile;
alter alter java class "<owner>.<name of Java Class>" resolve;
alter procedure <owner>.<name of the procedure> compile;
alter function <owner>.<name of the function> compile;
alter materialized view <owner>.<name of the materialized view> compile;

 

The script 'adcompsc.pls' under the $AD_TOP/sql :

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

 

The script 'utlrp.sql' under the DATABASE $ORACLE_HOME/rdbms/admin/

* This script must be run using SQL*PLUS.
* You must be connected AS SYSDBA to run this script.
* There should be no other DDL on the database while running the script.

 

Creating your own compilation script

With the following script, you will create a new sql-script to compile the invalid objects, which can be used to compile your individual invalid objects.

set pagesize 0 head off feedb off echo off
spool validate_all.sql

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects where object_type in ('PROCEDURE','FUNCTION','VIEW','TRIGGER','MATERIALIZED VIEW')
and status='INVALID' order by owner
/
select 'alter package '||owner||'.'||object_name||' compile package;'
from dba_objects where object_type in ('PACKAGE')
and status='INVALID' order by owner
/
select 'alter package '||owner||'.'||object_name||' compile body;'
from dba_objects where object_type in ('PACKAGE BODY')
and status='INVALID' order by owner
/
select 'ALTER JAVA SOURCE "' || object_name || '" COMPILE;'
from user_objects where object_type = 'JAVA SOURCE' and status = 'INVALID';
/
select 'ALTER JAVA CLASS "' || object_name || '" RESOLVE;'
from user_objects where object_type = 'JAVA CLASS' and status = 'INVALID';
/
spool off
exit

 

 

Some Questions & Answers

1. Why do invalid objects occur? 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 it is important to recompile the 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 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 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 Installation 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.

4. If we do not use the Product with the invalid objects, can we delete them?

In an E-Business Suite Installation are several dependencies between Products and therefore you should never delete any invalid object, if not advised by Metalink Support, that these invalid objects can be deleted.

5. How can I use adadmin to recompile my invalid objects ?

If you don't want to use the methods shown above, you can use adadmin in an E-Business Suite Environment to compile the invalid objects. Please execute following steps to initiate the compilation via 'adadmin':

1.      Open a new Shell and source the APPS User Environment

2.      Start 'adadmin' and pass the values requested for

3.      Select the menu '3. Compile/Reload Applications Database Entities menu'

4.      Select the menus '1. Compile APPS schema'

NOTE: Please be aware, that using adadmin to compile invalid objects, only the invalid objects for APPS will be compiled.