My Oracle Support Banner

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 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:

optimizer_mode ALL_ROWS
query_rewrite_enabled TRUE
query_rewrite_integrity trusted

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.