Check the status of a Materialized View and fix it.
Check the status of a Materialized View and Fix it
A materialized view is like any other view in that it is based on one or more underlying base tables.
Because of this, a change in the structure of an underlying table could cause the view based on the table to become invalid.
In fact, any change in any table referenced by a materialized view will cause the view to become invalid and unavailable for use.
A materialized view is validated each time it is referenced by a query. If the materialized view is determined to be invalid due to a change in an underlying table, an error is returned for the query that referenced it.
Checking for invalid Materialized Views
You can check the status of a materialized view by querying the USER_MVIEW_ANALYSIS or ALL_MVIEW_ANALYSIS data dictionary views. These views contain a column INVALID that will contain Y for valid materialized views and N for invalid materialized views. The query you would use to get this information is shown below:
SELECT MVIEW_NAME, INVALID
FROM USER_MVIEW_ANALYSIS
If a view is invalid, you can use the SQL command in the following diagram to revalidate the materialized view:
Required keywords
The name of the materialized view
ALTER MATERIALIZED VIEW mview_name
ENABLE QUERY REWRITE;
You can query USER_MVIEW_ANALYSIS to see the materialized views that support query rewrite. If a materialized view contains references to a remote table, it will not be listed in this view. You can query the owner of the materialized view, its name (Mview_Name), and the owner of the base table (Mview_Table_Owner). Many of the columns in this view are flags, such as Summary ('Y' if the view contains an aggregation), Known_Stale ('Y' if the view’s data is inconsistent with the base table), and Contains_Views ('Y' if the materialized view references a view). If the materialized view contains aggregations, you can query USER_MVIEW_AGGREGATES for details on the aggregations. Columns in this view are as follows:
Owner
Owner of the materialized view
Mview_Name
Name of the materialized view
Position_in_Select
Position within the query
Container_Column
Name of the column
Agg_Function
Aggregate function
DistinctFlag
'Y' if the aggregation uses the DISTINCT function
Measure
The SQL text of the measure, excluding the aggregate function
You can query details of the relations within materialized views from the USER_MVIEW_DETAIL_RELATIONS and USER_MVIEW_KEYS data dictionary views.
If the materialized view is based on joins, see USER_MVIEW_ JOINS for the join details. In general, USER_MVIEW_ANALYSIS will be the most commonly used data dictionary view related to materialized views.
