ORA-00942: table or view does not exist

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