Author: N. Kaparelis

Invalid Materialized View:

An Oracle Materialized View is basically a database object containing the results of a query. It is important to maintain the d ependencies related to materialized views so as to ensure the correct operation. Whenever a materialized view is created, it depends on the detail tables referenced in its definition. The materialized views may become invalid due to a number of reasons such as DDL operations on any dependency in the materialized view however it is revalidated automatically whenever it is referenced.

Revalidating an Invalid Materialized View:

ALTER MATERIALIZED VIEW  mview_name ENABLE QUERY REWRITE command should be used in the situations where a column referenced by a materialized view is dropped in a table or the scenario where the owner of the materialized view does not have the privileges to perform query rewrite otherwise you will get an error.

Determining the State of the Materialized View using Tables:

The USER_MVIEWS or ALL_MVIEWS tables show all materialized views in the database. You can query the tables USER_MVIEWS or ALL_MVIEWS to determine the state of a materialized view. The column STALENESS in these tables indicates the staleness state of the materialized view. The values FRESH, STALE, UNUSABLE, UNKNOWN, or UNDEFINED in the STALENESS column will help you find out if the materialized view can be used or not.

SELECT MVIEW_NAME, STALENESS

FROM USER_MVIEWS

WHERE MVIEW_NAME = 'MY_VIEW';

Determining the State of the Materialized View using Views:

You can also determine the state of a materialized view by querying the DBA_MVIEW_ANALYSIS view. This view describes all materialized views in the database that potentially support query rewrite and that provide additional information for analysis by applications. DBA_MVIEW_ANALYSIS view contains the same columns as in ALL_MVIEW_ANALYSIS.

The column UNUSABLE contains the values Y or N. If UNUSABLE value is set to Y then it means that the materialized view is unusable and hence it cannot be used.

SELECT MVIEW_NAME, UNUSABLE, KNOWN_STALE, INVALID

FROM DBA_MVIEW_ANALYSIS

WHERE OWNER = 'MY_VIEW' AND INVALID = 'Y';

Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Step by Step Guide for Super fast Database Cloning!!
   DBA Tips for Oracle Regular Expressions!!
   Beware of Database Myths
   A Quick Guide to determine Oracle RAM Size!!