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:
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 of the materialized view
Name of the materialized view
Position within the query
Name of the column
'Y' if the aggregation uses the DISTINCT function
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.