rem =========================================================================
rem 
rem                     invalid_objects.sql
rem 
rem     Copyright (C) Oriole Software, 2000
rem 
rem     Downloaded from http://www.oriolecorp.com
rem 
rem     This script for Oracle database administration is free software; you
rem     can redistribute it and/or modify it under the terms of the GNU General
rem     Public License as published by the Free Software Foundation; either
rem     version 2 of the License, or any later version.
rem 
rem     This script is distributed in the hope that it will be useful,
rem     but WITHOUT ANY WARRANTY; without even the implied warranty of
rem     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
rem     GNU General Public License for more details.
rem 
rem     You should have received a copy of the GNU General Public License
rem     along with this program; if not, write to the Free Software
rem     Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
rem 
rem =========================================================================
-- 
--    To be run by a DBA
--    Lists which are the invalid objects and tells why they are invalid ...
--
column invalid_object format A30
column likely_reason format A35 word_wrapped
set recsep off
break on type on invalid_object
select owner || '.' || object_name invalid_object,
       '--- ' || object_type || ' ---' likely_reason
from dba_objects
where status = 'INVALID'
union
select d.owner || '.' || d.name,
       'Non-existent referenced db link ' || d.referenced_link_name
from dba_dependencies d
where not exists (select 'x'
                  from dba_db_links
                  where owner in ('PUBLIC', d.owner)
                    and db_link = d.referenced_link_name)
  and d.referenced_link_name is not null
  and (d.owner, d.name, d.type) in (select owner, object_name, object_type
                        from dba_objects
                        where status = 'INVALID')
union
select d.owner || '.' || d.name,
       'Depends on invalid ' || d.referenced_type || ' '
              || d.referenced_owner || '.' || d.referenced_name
from dba_objects ro,
     dba_dependencies d
where ro.status = 'INVALID'
  and ro.owner = d.referenced_owner
  and ro.object_name = d.referenced_name
  and ro.object_type = d.referenced_type
  and d.referenced_link_name is null
  and (d.owner, d.name, d.type) in (select owner, object_name, object_type
                        from dba_objects
                        where status = 'INVALID')
union
select d.owner || '.' || d.name,
       'Depends on newer ' || d.referenced_type || ' '
              || d.referenced_owner || '.' || d.referenced_name
from dba_objects ro,
     dba_dependencies d,
     dba_objects o
where nvl(ro.last_ddl_time, ro.created) > nvl(o.last_ddl_time, o.created)
  and ro.owner = d.referenced_owner
  and ro.object_name = d.referenced_name
  and ro.object_type = d.referenced_type
  and d.referenced_link_name is null
  and d.owner = o.owner
  and d.name = o.object_name
  and d.type = o.object_type
  and o.status = 'INVALID'
union
select d.owner || '.' || d.name,
       'Depends on ' || d.referenced_type || ' '
              || d.referenced_owner || '.' || d.referenced_name
              || decode(d.referenced_link_name,
                        NULL, '',
                        '@' || d.referenced_link_name)
from dba_dependencies d
where d.referenced_owner != 'PUBLIC'  -- Public synonyms generate noise 
  and d.referenced_type = 'NON-EXISTENT'
  and (d.owner, d.name, d.type) in (select owner, object_name, object_type
                        from dba_objects
                        where status = 'INVALID')
union
select d.owner || '.' || d.name invalid_object,
       'No privilege on referenced ' || d.referenced_type || ' '
                      || d.referenced_owner || '.' || d.referenced_name
from dba_objects ro,
     dba_dependencies d
where not exists (select 'x'
                  from dba_tab_privs p
                  where p.owner = d.referenced_owner
                    and p.table_name = d.referenced_name
                    and p.grantee in ('PUBLIC', d.owner))
  and ro.status = 'VALID'
  and ro.owner = d.referenced_owner
  and ro.object_name = d.referenced_name
  and d.referenced_link_name is not null
  and (d.owner, d.name, d.type) in (select owner, object_name, object_type
                        from dba_objects
                        where status = 'INVALID')
union
select o.owner || '.' || o.object_name,
       e.text
from dba_errors e,
     dba_objects o
where e.text like 'PLS-%'
  and e.owner = o.owner 
  and e.name = o.object_name
  and e.type = o.object_type
  and o.status = 'INVALID'
/