-- #############################################################################################
--
-- %Purpose: Show all INVALD Objects in the Database for all Users except SYS and SYSTEM
--
-- Use:      SYSTEM, SYS or user having SELECT ANY TABLE system privilege
--
-- #############################################################################################
--
set verify off
set pagesize 200
set feedback off
column owner format A15
column object_name format A30 heading 'Object'
column object_id format 999999 heading "Id#"
column object_type format A15
column status format A8
ttitle left 'Invalid Objects found for ...'skip 2
--
-- Lists all invalid objects for a database
--
spool list_invalid_objects.lst
set termout off
--
SELECT owner, object_name, object_id, object_type, status
 FROM dba_objects
WHERE status != 'VALID'
  AND owner NOT IN ('SYS','SYSTEM');
--
spool off
--
-- Create script which can be used to recompile
-- all of the invalid objects
--
ttitle off
set concat +
spool compile_invalid_objects.sql
set concat .
set feedback off
set heading off
set pagesize 999
set verify off

SELECT DISTINCT 'sqlplus '||owner||'/'||owner||'<<EOF'||chr(10)||
'prompt Compiling '||owner||' objects...'||chr(10)||
'execute dbms_utility.compile_schema('||chr(39)||owner||chr(39)||');'||
chr(10)||'show err;'||chr(10)||'quit'||chr(10)||'EOF'
 FROM dba_objects
WHERE status != 'VALID'
  AND object_type != 'VIEW'
  AND owner NOT IN ('SYS','SYSTEM');

SELECT 'sqlplus '||owner||'/'||owner||'<<EOF'||chr(10)||
'prompt Compiling '||owner||' views...'||chr(10)||
'alter view '||object_name||' compile;'||chr(10)||
'show errors view '||object_name||';'||chr(10)||'quit'||chr(10)||'EOF'
 FROM dba_objects
WHERE status != 'VALID'
  AND object_type = 'VIEW'
  AND owner NOT IN ('SYS','SYSTEM');

spool off
set termout on

PROMPT
PROMPT list_invalid_objects.lst has been spooled
PROMPT compile_invalid_objects.sql has been spooled
PROMPT