Hi,
Version 11.2.0.3
We were getting an ORA-00904 error invalid identifier and sql which was throwing the error didn't have the alias shown in the error message.
By changing the sql statement for a materialized view was able to identify the mview which causing the error. The mview itself does not have query rewrite enabled but is set at database level. One of the dimension database objects didn't have the column being referenced in the mview and marked as INVALID hence the message 0RA-00904 invalid identifier.
This suggests query rewrite happening as intermittent and error shown not directly the sql which trying to run but rather from a mview.
I have a few questions
-
How can we best see all the sqls in a given timeframe which being rewritten by ORACLE transparently to source data from materialized views and dimensions ?
-
Why woudl query be rewritten if the mview itself has rewrite enabled set to N in user_mviews?
sounds like a bug?
-
When we query select * from user_mviews takes long time and have to kill after 10 minutes.
If we run s
select *
from user_mviews u
where u.mview_name = <mview_name> this is very quick/instant for some mviews but doesn't return for others.
Is there some some sort of corruption at data dictionary level and how could we dtermine this or is there some other reason for this?
Thanks