Saturday 14 March 2015

How to list and compile Invalid objects

1) To list the Invalid objects:-

SELECT count(OBJECT_NAME),OBJECT_TYPE from dba_objects WHERE  status = 'INVALID' group by object_type;


2) To Compile the Invalid objects:-

EXEC UTL_RECOMP.recomp_serial('schema name');

Orinvalid.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;'fromdba_objectswherestatus = 'INVALID'and object_type in ('PACKAGE','FUNCTION','PROCEDURE');


spool off;
set heading on;set feedback on;set echo on;
@run_invalid.sql


Or 

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


In Application side.

a) Login as application tier user (avisr12 in my case)
b) Set environment variable (under $INSTALL_DIR/apps/apps_st/appl/APPS[sid]_[hostname].env)
c) admin
d) option 3 “compile/reload Applications Database Entities menu
e) option 1 “Compile Apps Schema”

References:

NOTE: Please be aware, that using adadmin to compile invalid objects, only the invalid objects for APPS will be compiled.
References
NOTE:1019928.6 - Script: To verify Stored Procedures
NOTE:331169.1 - Using Fully Qualified Database Link Name With a Hyphen in the Domain Name From PL/SQL Fails With ORA-02083, But Not From Regular SQL
NOTE:429252.1 - Forms With Dblink Fails With ORA-01041 ORA-03113
NOTE:73995.1 - Script: To Recompile Invalid Objects
NOTE:1325394.1 Troubleshooting Guide - invalid objects in the E-Business Suite Environment 11i and 12 

No comments:

Post a Comment