Purpose
-------
To provide a step by step plan to solve invalid objects.
Scope & Application
-------------------
This note is intended to help analysts and customers to solve invalid objects in a quick and successful manner.
**Please read entire document before starting**
S T E P 1
--------------------------------------
Find out exactly how many invalid objects you have.
There are many ways to do this. Depending on how detailed you want to get, you must first know what to query on.
SQL> desc DBA_OBJECTS;
Will give you a list of the different names of the objects to query on:
Name Null? Type
--------------------- -------- ----
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(15)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED
The most commonly used dba objects are:
COUNT: Number of objects returned
OBJECT_NAME: Name of the object
STATUS: Tells if the object is Valid or Invalid
OWNER: The name of the schema in which the invalid object belongs to
OBJECT_TYPE: Type of object. Ex: package, package body, view or trigger
To determine how many invalid objects you have:
SELECT OWNER, COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
GROUP BY OWNER;
OWNER COUNT(*)
------------ ----------
APPS 218
INV 1
SYS 3
SYSTEM 1
S T E P 2
--------------------------------------
Recompile invalid objects using ADADMIN:
Unix Operating System
---------------------
a. Log in as applmgr:
b. Go to the $AD_TOP and 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
10.7 Task 7 Compile APPS schema(s)
11.0.x Task 2 Compile APPS schema(s)
11.5.x Task 2 Compile APPS schema(s)
When it is finished, make sure you view the log file for errors(if you took the default it is located in the prodappl/admin/PROD/log). Depending on the size of your instance, the ADADMIN can take anywhere from 30 min. to a couple of hours.
Windows NT Operating System
---------------------------
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
10.7 Task 5 Compile APPS schema's)
11.0 Task 2 Compile APPS schema's)
11.5.x Task 2 Compile APPS schema's)
For more information on using ADADMIN please see Note 99768.1 and/or
Note 74661.1.
Also more information can be found in the following manuals.
Oracle Applications Installation Manual Release 10.7 for Unix pg. 6-32
Oracle Applications Installation Manual Release 11 for Unix pg. 5-7
Oracle Applications Installation Manual Release 11 for NT pg. 5-6
Maintaining Oracle Applications, Release 11i 11.5.3 (Jan 2001) pg. 2-1
Maintaining Oracle Applications, Release 11i 11.5.2 (Oct 2000) pg. 2-1
S T E P 3
--------------------------------------
Recompile all invalid objects using ADCOMPSC.pls
(Applications 10.7 - 11.0.x)
The script can be run as follows:
cd $AD_TOP/sql
sqlplus apps/apps @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD %
Example: sqlplus apps/apps @adcompsc.pls apps apps %
sqlplus apps/apps @adcompsc.pls system manager %
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 object stops decreasing.
NOTE: APPS_DDL and APPS_ARRAY_DDL should exist in all schema's. If it does not or you Receive an error:
'APPLSYS.APPS_DDL' must be declared
- If you encounter an ORA-1555 error while running adcompsc.pls, just restart the script.
Next use aderrchk.sql to record the remaining invalid objects. This script is also located in the $AD_TOP/sql directory. You can send the aderrchk.sql to a file using the spool
Ex: sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD %
It will ask you for values 4 and 5.
4 - File to spool to. When running from adaimgr, this would be something like errchk
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.
If you do not have many invalid objects Go to step 5. If you still have many invalid objects go to Step 4.
Recompile all invalid objects using ADVRFAPP.SQL
For Applications 11.5.x
-------------------------------------------------------
The script can be run as follows:
For UNIX users:
$ cd $AD_TOP/admin/sql
$ sqlplus system username>/
An output file, LAPPS schema name>.lst, is produced for each APPS schema. The file is located at $APPL_TOP/admin/
For NT users:
C:\> cd %AD_TOP%\admin\sql
C:\> sqlplus system username>/
An output file, LAPPS schema name>.lst, is produced for each APPS schema. The file is located in %APPL_TOP%\admin\
is the value of your LOCAL variable.
Next use aderrchk2.sql to record the remaining invalid objects. This script is also located in the $AD_TOP/sql directory. You can send the aderrchk.sql to a file using the spool
Ex: sqlplus x/y @aderrchk2.sql SCHEMA_NAME SCHEMA_PASSWORD %
Next, go to step 5
S T E P 4
---------------------------------------
Use the Compile All script
( 10.7 and 11.0.x Relase ONLY )
This is a great script for recompiling invalid objects. PLEASE NOTE: The following script is provided for reference purposes only. This is not supported by Oracle Support.
rem *********************************************************
rem * file: comp_all.sql
rem * purpose: compile all database stored objects.
rem * to use: log in as system account then
rem * execute this script using the following syntax:
rem *
rem * SQL> @comp_all
rem *
rem * NOTE: You should not have to run this script more
rem * than once since it uses
rem * order_object_by_dependency table to compile
rem * objects in the proper order. Any
rem * compilation errors generated should be
rem * investigated.
rem *********************************************************
set heading off
set pagesize 0
set linesize 79
set verify off
set echo off
spool comp_all.tmp
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects A,
sys.order_object_by_dependency B
where
A.OBJECT_ID = B.OBJECT_ID(+) and
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
order by
DLEVEL DESC,
OBJECT_TYPE,
OBJECT_NAME;
spool off
S T E P 5
----------------------------------------
Find the dependents.
Some objects me be dependent on other objects and will not compile until the dependent object is compiled first. The following script will give you the objects that are the dependents which you should try to recompile first.
PLEASE NOTE: The following script is provided for reference purposes only.
This is not supported by Oracle Support. (written by Paul Ferguson 12/98)Run script from the APPS schema.
REM
REM inval_depend.sql - List invalid objects + dependencies
REM
REM Paul Ferguson 12/98
REM
REM
REM This script should be run from the APPS schema
REM
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
Below is the output from the script. You must first compile the dependencies (objects on the right) before you compile the dependent (object(s) on the left.
Invalid objects - Dependencies
Object name Type Owner Type Depends on:
----------- ------------ ------- --------- ----------
ADI_BINARY_FILE PACKAGE BODY APPLSYS TABLE FND_PROFILE_OPTION_VALUES
APPS PACKAGE ADI_BINARY_FILE
APPS PACKAGE ADI_HEADER_FOOTER
BEN_APF_UPD PACKAGE APPS PACKAGE BEN_APF_BUS
APPS PACKAGE BEN_APF_DEL
S T E P 6
---------------------------------------
Manually recompile remaining invalid objects.
Run the following script, which will give you a detailed list of invalid objects.
column object_name format A18 heading "Object name"
column object_type format A12 heading "Type"
column referenced_owner format A12 heading "Owner"
select object_name, object_type, owner
from dba_objects
where status ='INVALID';
Connect to SQL*PLUS as the owner of the object (generally apps). Use one of the following commands depending on the object type:
1. Package SQL> alter package
Ex: alter package apps.APPS_ARRAY_DDL compile;
2. Package body SQL> alter package
Ex: alter package apps.APPS_ARRAY_DDL compile body;
3. View SQL> alter view
Ex: alter view apps.ICX_QUOTE_LINES_V compile;
4. Trigger SQL> alter trigger
Ex: alter trigger apps.PO_INTERSHIP_T compile;
If the object compiles with warnings, use either of the following scripts to view the errors.
SQL> show errors
or
SQL> select * from user_errors where name = '
S T E P 7
---------------------------------------
Recreate the object.
Another way to solve an invalid object is to run the SQL statement that recreates the object and then manually recompile the object. (for instructions on manually recompiling an object please see Step 6) To do this you must run the following script.
select text
from user_source
where name = 'OBJECTNAME'
and text like '%Header%';
Once it gives you the SQL statement to run, do the following:
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';
or you can run the following SQL statement:
NOTE: The following script is provided for reference purposes only.
This is not supported by Oracle Support. (written by Paul Ferguson 12/98) This script should be run from the APPS schema.
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
S T E P 8
----------------------------------
Search
Before opening an iTar with support, Please do a search on Metalink with the following:
-