Invalid Object Why?????????????

SELECT owner || '.' || object_name invalid_object,'--- ' || object_type || ' ---' likely_reason

FROM dba_objects WHERE status = 'INVALID' AND owner = 'LDBO'

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'

)

AND d.owner = 'LDBO'

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'

)

AND d.owner = 'LDBO'

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' AND d.owner = 'LDBO'

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'

)

AND owner = 'LDBO'

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'

)

AND d.owner = 'LDBO'

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' AND o.owner = 'LDBO'

/