-- total number of invalids
select count(*) from dba_objects o where o.STATUS='INVALID';
-- show all invalids
select * from dba_objects o where o.STATUS='INVALID';
-- show all invalids in schema WEBAPP
select * from dba_objects o where o.STATUS='INVALID' and o.OWNER='WEBAPP';
--  show all invalids with prefix "MY_"
select * from dba_objects o where o.STATUS='INVALID' and o.OBJECT_NAME like 'MY_%';

-- Query returning the number of invalid objects remaining. This number should decrease with time.
SELECT COUNT(*) FROM sys.obj$ WHERE status IN (4, 5, 6);
-- Query returning the number of objects compiled so far. This number should increase with time.
SELECT COUNT(*) FROM sys.UTL_RECOMP_COMPILED;
-- Query showing jobs created by UTL_RECOMP
SELECT job_name FROM dba_scheduler_jobs WHERE job_name like 'UTL_RECOMP_SLAVE_%';
-- Query showing UTL_RECOMP jobs that are running
SELECT job_name FROM dba_scheduler_running_jobs   WHERE job_name like 'UTL_RECOMP_SLAVE_%';

SET head OFF;
SET echo OFF; 
SET termout OFF; 
--SET verify OFF; 
set pagesize 0;
spool _tmp_compile_invalids.sql;
--
SELECT 'ALTER PACKAGE ' || owner || '.' || object_name || ' COMPILE PACKAGE;'
           code
  FROM dba_objects
 WHERE status = 'INVALID' AND object_type LIKE 'PACKAGE%'
UNION
SELECT 'ALTER PROCEDURE ' || owner || '.' || object_name || ' COMPILE;' code
  FROM dba_objects
 WHERE status = 'INVALID' AND object_type LIKE 'PROCEDURE'
UNION
SELECT 'ALTER FUNCTION' || owner || '.' || object_name || ' COMPILE;' code
  FROM dba_objects
 WHERE status = 'INVALID' AND object_type LIKE 'FUNCTION'
UNION
SELECT 'ALTER SYNONYM ' || owner || '.' || object_name || ' COMPILE;' code
  FROM dba_objects
 WHERE     status = 'INVALID'
       AND object_type LIKE 'SYNONYM%'
       AND owner <> 'PUBLIC'
UNION
SELECT 'ALTER PUBLIC SYNONYM ' || object_name || ' COMPILE;' code
  FROM dba_objects
 WHERE     status = 'INVALID'
       AND object_type LIKE 'SYNONYM%'
       AND owner = 'PUBLIC'
UNION
SELECT    'ALTER  VIEW ' || owner || '.'|| object_name || ' COMPILE;'   code
  FROM infra.dba_objects_new_invalid
 WHERE status = 'INVALID' AND object_type LIKE 'VIEW%'
 UNION
SELECT    'ALTER MATERIALIZED VIEW '
       || owner
       || '.'
       || object_name
       || ' COMPILE;'
           code
  FROM dba_objects
 WHERE status = 'INVALID' AND object_type LIKE 'MATERIALIZED VIEW%';    
--
spool off;
SET echo ON;
SET termout ON; 
--
@_tmp_compile_invalids.sql;

exit;