-- #############################################################################################
--
-- %Purpose:    Recompile all INVALID DB-Objetcs with Dependency Tracking (Very handy Script)
--
-- Environment: Execute under DB-User SYSTEM or SYS
--
-- #############################################################################################
--
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;













spool comp_all.tmp

select decode (OBJECT_TYPE, 'PACKAGE BODY',
               'alter package ' || OWNER ||'.'||OBJECT_NAME || ' compile body;',
               'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;'
              )
from   DBA_OBJECTS A,
       SYS.ORDER_OBJECT_BY_DEPENDENCY B
where  A.OBJECT_ID = B.OBJECT_ID (+)
and    A.STATUS = 'INVALID'
and    A.OBJECT_TYPE in ('PACKAGE BODY','PACKAGE','FUNCTION','PROCEDURE','TRIGGER','VIEW')
order by B.DLEVEL desc,
         A.OBJECT_TYPE,
         A.OBJECT_NAME;
--
spool off;
set heading on;
set feed on;
set scan on;
set verify on;
--
@comp_all.tmp