Its been a while since I had anything worth sharing, but here is a small note on identifying which table or view is missing in a complex view select statement.
If you look at DBA_ERRORS for an invalid view,all you’ll see is a ORA-00942: table or view does not exist.
You need something a bit deeper to locate the table. Simple but effective.
select owner, name, type, referenced_owner, referenced_name, referenced_type from dba_dependencies where (owner, name) in
(select owner, object_name from dba_objects where objecT_type = 'VIEW' and status <> 'VALID')
and not exists (select 1 from dba_objects a where referenced_owner = a.owner and referenced_name = a.object_name and referenced_type = a.object_type)
/
All this does is search through the dependencies of any invalid views looking for a missing object.
Happyheth
