How to find Invalid Objects and Compile in Oracle Finding Invalid Objects and CompilingObjects that requires recompilation are---------------------------------------------------- VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED , JAVA CLASS, TYPE, TYPE BODYFind Invalid Objects--------------------------- set pages 50000 lines 32767 select OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID' and OWNER in ('&OWNER') GROUP BY OWNER, OBJECT_TYPE ORDER BY OBJECT_TYPE;SPOOL The Invalids list --------------------------- spool generate_invalids.lst set pages 50000 lines 32767 col owner format a15 col object_type format a20 col object_name format a30 select owner, object_name, object_type,status,created,last_ddl_time from dba_objects where status = 'INVALID' and owner in ('&owner') order by object_type, object_name desc / spool offRecompile Invalid Objects----------------------------------- spool recompile_invalids.lst select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type) || ' ' ||owner|| '."'||object_name||'" '||decode(object_type,'PACKAGE BODY','COMPILE BODY','compile')|| ' ; ' from dba_objects where object_type in ('PACKAGE','PACKAGE BODY','PROCEDURE','VIEW', 'TRIGGER','FUNCTION','SYNONYM') and status='INVALID' and owner in ('&owner') / spool off @recompile_invalids.lst Validation---------- set pages 50000 lines 32767 col owner format a15 col object_type format a20 col object_name format a30 select owner, object_name, object_type,status,created,last_ddl_time from dba_objects where owner in ('&owner') and trunc(last_ddl_time) = trunc(sysdate) order by object_type, object_name / exit ------------------------------------------------------------------------------------------------------------------MORE QUERIES Generate Oracle scripts to compile procedure , function , package , package body,trigger, view :- select decode( OBJECT_TYPE, 'PACKAGE BODY', 'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;', 'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' ) from dba_objects a where STATUS = 'INVALID' and OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' ) and owner='SYS' order by OBJECT_TYPE, OBJECT_NAME; ------------------------------------------------------------------------------------------------------------------ set heading off; set feed off; set pagesize 10000; set wrap off; set linesize 200; set tab on; set scan off; set verify off; -- SPOOL gen_inv_obj.sql; select decode (OBJECT_TYPE, 'PACKAGE BODY', 'alter package ' || a.OWNER||'.'||OBJECT_NAME || ' compile body;', 'alter ' || OBJECT_TYPE || ' ' || a.OWNER||'.'||OBJECT_NAME || ' compile;') from dba_objects a, (select max(level) order_number, object_id from public_dependency connect by object_id = prior referenced_object_id group by object_id) b where A.object_id = B.object_id(+) and STATUS = 'INVALID' and OBJECT_TYPE in ('PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE','TRIGGER', 'VIEW') order by order_number DESC,OBJECT_TYPE,OBJECT_NAME; SPOOL off; @gen_inv_obj.sql; ------------------------------------------------------------------------------------------------------------------ set serverouput on DECLARE comp_pack VARCHAR2 (100); comp_pack_body VARCHAR2 (200); comp_view VARCHAR2 (200); comp_proc VARCHAR2 (200); comp_trig VARCHAR2 (200); comp_func VARCHAR2 (200); BEGIN FOR c IN ( SELECT * FROM dba_objects WHERE status = 'INVALID' ORDER BY object_type) LOOP BEGIN --generate compile statement comp_pack :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile;'; comp_pack_body :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile body;'; comp_view :='alter view ' || c.owner || '.' || c.object_name || ' compile;'; comp_proc :='alter procedure '|| c.owner|| '.'|| c.object_name|| ' compile;'; comp_func :='alter function '|| c.owner|| '.'|| c.object_name|| ' compile;'; comp_trig :='alter trigger '|| c.owner|| '.'|| c.object_name|| ' compile;'; DBMS_OUTPUT.put_line ('Compile -> ' || c.object_name || ' type : ' || c.object_type); --compile IF c.object_type = 'PACKAGE' THEN EXECUTE IMMEDIATE comp_pack; ELSIF c.object_type = 'PACKAGE BODY' THEN EXECUTE IMMEDIATE comp_pack_body; ELSIF c.object_type = 'VIEW' THEN EXECUTE IMMEDIATE comp_view; ELSIF c.object_type = 'PROCEDURE' THEN EXECUTE IMMEDIATE comp_proc; ELSIF c.object_type = 'FUNCTION' THEN EXECUTE IMMEDIATE comp_func; ELSIF c.object_type = 'TRIGGER' THEN EXECUTE IMMEDIATE comp_trig; END IF; --catch exception and show EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Compile ERROR : '|| c.owner|| '.'|| c.object_name|| ' type => '|| c.object_type); END; END LOOP; END; ------------------------------------------------------------------------------------------------------------------