QSM-01150: Query Did Not Rewrite For A User Other Than The Owner Of the Materialized View
(Doc ID 1594725.1)
Last updated on FEBRUARY 20, 2019
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
The defining query of the Materialized View references SYNONYMs, not just the source objects such as other tables and views.
A call to DBMS_MVIEW.EXPLAIN_REWRITE with the same query (text) that is used in the SELECT clause of the Materialized View, the query is returned as "not rewritten". It only shows as "rewritten" if the DBMS_MVIEW.EXPLAIN_REWRITE command is run as the same user that owns the Materialized View.
A Materialized View defined as "ENABLE QUERY REWRITE" is utilized for query rewrite only for the user that owns the Materialized View.
Other users do not benefit from the query rewrite.
The relevant parameters are:
The system privilege "GLOBAL QUERY REWRITE" is granted to the user executing the query. This should allow query rewrite to use Materialized Views owned by other schemas / users, but the query still does not get rewritten.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document