How to compile the invalid objects in R12.2

Find the invalid objects:

1. Find the number of invalid objects

Select count(*) from dba_objects where status=’INVALID’;

2. Find the object name /type

Select owner,object_type,status from dba_objects where status ='INVALID';

How to compile invalid objects

1 Compile the invalid objects in apps schema:

Application Side:

Login the application services

In application side use ADadmin utlity and give below options

adadminà option3 Compile/Reload Applications Database Entities menuà1: Compile APPS schema

Compile entire schema

SQL> EXEC UTL_RECOMP.recomp_serial('APPS');

PL/SQL procedure successfully completed.

SQL>

2 Database Side:

Login Database server

Goto $ORACLE_HOME/rdbms/admin and run utlrp.sql

SQL> @utlrp.sql

3 Manual Method:

Alter package <owner>,<package_name> compile;

Alter package <owner>,<package_name> compile body;

Alter view <owner>,<view_name> compile;

Alter materialized view <owner>.<Package_name> Compile;

Alter procedure <owner>,<procedure_name> compile;

Alter function <owner>,<function_name> compile;

Alter synonym <synonym_name> compile;

Alter trigger <trigger_name> compile;

In case you have lots of invalid objects, you can generate scripts that will generate

the sqls for compiling the invalid objects :

In sqlplus connect as sys:

set heading off

spool compileinvalid.sql

select 'alter '||object_type|| ' ' || owner ||'.'||object_name || ' compile;' from dba_objects where status='INVALID';

spool off

Then run invalid.sql in sqlplus prompt as sys user