Compilation of Invalids

select decode(object_type,'PACKAGE BODY','alter package ',concat('alter ',object_type))||
' '||owner||'.'||object_name|| decode(object_type,'PACKAGE BODY',' compile body ;',' compile ;')
from   dba_objects where  status='INVALID'
/

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

SELECT OWNER,COUNT(OBJECT_NAME) INVALIDS FROM DBA_OBJECTS WHERE STATUS ='INVALID'  GROUP BY OWNER;
 exec sys.utl_Recomp.recomp_parallel (16);


 @ORACLE_HOME/rdbms/admin/utlrp.sql
=====================================================================================================================
COL OWNER FOR A10
COL OBJECT_NAME FOR A30
COL OBJECT_TYPE FOR A20
COL CREATED FOR A15
 set linesize 2000
 select owner,object_name,object_type,created from dba_objects where status='INVALID' order by created asc;

 select owner, object_name, object_type from dba_objects where status like 'INVALID' order by owner;

 select object_name,object_type,owner from dba_objects where status='INVALID' and object_name not in (select object_name from Invalid_table);
 select object_name,object_type,owner from dba_objects a where status = 'INVALID' and not exists (select 0 from Invalid_table b where b.object_name=a.object_name and b.owner=a.owner);

=========================================================================================================================

 alter [object] [object_name] compile;

 1. From Database Tier
a) Login as database tier user (ovisr12 in my case)
b) Set environment variable (under $INSTALL_DIR/db/tech_st/[10.2.0 or 11.1.0]/[sid]_[hostname].env)
c) cd $ORACLE_HOME/rdbms/admin
d) sqlplus “/as sysdba”
e) SQL> @utlrp.sql


I. Using application tier (adadmin)
-Login as application tier user
11i

    Set environment variable from $APPL_TOP/APPSORA.env)
    adadmin
    option 3 compile/reload Applications Database Entities menu
    option 1 Compile Apps Schema”

Release 12

    Set environment variable (under $INSTALL_DIR/apps/apps_st/appl/APPS[sid]_[hostname].env)
    adadmin
    option 3 compile/reload Applications Database Entities menu
    option 1 Compile Apps Schema”


IV. ADCOMPSC.pls

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.
In case of an ORA-1555 error while running adcompsc.pls, restart the script.

The script can be run as followed :

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

SQL> @adcompsc.pls apps apps %





 select do.name dname, po.name pname, d.p_timestamp, po.stime p_stime
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where d.P_OBJ#=po.obj#(+)
and d.D_OBJ#=do.obj#
and do.status=1 /*dependent is valid*/
and po.status=1 /*parent is valid*/
and po.stime!=d.p_timestamp /*parent timestamp not match*/
and do.type# not in (28,29,30) /*dependent type is not java*/
and po.type# not in (28,29,30) /*parent type is not java*/
order by 2,1;

invalid.sql

Set heading off;
set feedback off;
set echo off;
Set lines 999;

Spool run_invalid.sql

select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;' from dba_objects where status = 'INVALID' and object_type in ('PACKAGE','FUNCTION','PROCEDURE');

spool off;
set heading on;
set feedback on;
set echo on;

@run_invalid.sql