How to find Invalid Objects and Compile in Oracle

How to find Invalid Objects and Compile in Oracle

Finding Invalid Objects and Compiling

Objects that requires recompilation are
----------------------------------------------------
VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED , JAVA CLASS, TYPE, TYPE BODY

Find 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 off


Recompile 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;