Lesson 6	Handling invalidation
Objective	Check the status of a Materialized View and fix it.

Checking and Repairing Materialized Views in Modern Oracle

Materialized views are built on top of one or more base tables. As those underlying tables change over time—through data modifications or structural changes—the materialized view can become stale, invalid, or even unusable for query rewrite.

In current Oracle releases (11g and later, including cloud-enabled versions and Oracle 23c/23ai), Oracle automatically tracks the health of each materialized view. Your job as a DBA is to:

Detect whether the materialized view is still usable and fresh enough for its purpose.
Determine why it became stale or invalid.
Choose the appropriate repair action—compile, refresh, or rebuild.

How Materialized Views Become Stale or Invalid

When you create a materialized view, Oracle records dependencies on the detail (base) tables and other objects referenced in its defining query. Over time, several kinds of changes can affect its status:

DML on base tables (INSERT, UPDATE, DELETE) makes the data in the materialized view stale until the next refresh.
DDL on base tables (for example, dropping a column, changing datatypes, or renaming objects) can invalidate the materialized view definition.
Privilege changes (for example, adding query rewrite privileges after the fact) can cause Oracle to mark the view as needing recompilation.

Oracle revalidates a materialized view when it is referenced and, in many cases, this happens transparently. When automatic revalidation is not possible, you must explicitly repair the materialized view.

Checking Status with USER_MVIEWS / ALL_MVIEWS

The primary way to check the status of a materialized view in modern Oracle is to query the data dictionary views USER_MVIEWS, ALL_MVIEWS, or DBA_MVIEWS. These views expose key status columns such as STALENESS, COMPILE_STATE, and LAST_REFRESH_DATE.

A simple diagnostic query in your own schema might look like this:


SELECT
    mview_name,
    staleness,
    compile_state,
    last_refresh_type,
    last_refresh_date,
    query_rewrite_enabled
FROM   user_mviews
ORDER  BY mview_name;

The STALENESS column is especially important. It can contain values such as:

FRESH – The data is in sync with the base tables.
STALE – Base tables have changed since the last refresh.
UNUSABLE – The materialized view cannot be used until a complete refresh.
UNKNOWN or UNDEFINED – Oracle cannot determine staleness.
NEEDS_COMPILE – The definition must be recompiled before you can trust the status.

These values allow you to build a straightforward “health check” for materialized views as part of your regular monitoring.

Using USER_MVIEW_ANALYSIS and Related Views

For more detailed analysis—especially when you are investigating why query rewrite is not using a materialized view—you can query additional dictionary views, including:

USER_MVIEW_ANALYSIS / ALL_MVIEW_ANALYSIS – Summarizes properties relevant to query rewrite, such as whether the materialized view is a summary, contains views, or is known to be stale.
USER_MVIEW_AGGREGATES – Describes aggregate expressions used in the materialized view.
USER_MVIEW_DETAIL_RELATIONS and USER_MVIEW_KEYS – Show relationships and key information for the detail tables.
USER_MVIEW_JOINS – Shows join relationships used in the materialized view definition.

For example, you might query USER_MVIEW_AGGREGATES to inspect how aggregates are defined:

Column	Description
`OWNER`	Owner of the materialized view.
`MVIEW_NAME`	Name of the materialized view.
`POSITION_IN_SELECT`	Position of the aggregate expression in the SELECT list.
`CONTAINER_COLUMN`	Name of the column in the materialized view that stores the aggregate.
`AGG_FUNCTION`	Aggregate function used (for example, `SUM`, `COUNT`, `AVG`).
`DISTINCTFLAG`	`'Y'` if the aggregate uses `DISTINCT`, otherwise `'N'`.
`MEASURE`	Expression being aggregated, excluding the aggregate function itself.

These views are especially useful when you are troubleshooting why a materialized view is not eligible for fast refresh or query rewrite.

Revalidating a Materialized View

When the STALENESS column reports NEEDS_COMPILE, or when structural changes have occurred, you can revalidate the materialized view definition with:


ALTER MATERIALIZED VIEW mview_name COMPILE;

This compilation step is inexpensive and updates the metadata so Oracle can correctly evaluate whether the materialized view is usable and rewriteable.

In some cases, you also want to ensure that the materialized view can be considered for query rewrite:


ALTER MATERIALIZED VIEW mview_name
  ENABLE QUERY REWRITE;

Enable query rewrite so the optimizer can substitute this materialized view when it is fresh (or when your QUERY_REWRITE_INTEGRITY settings allow stale data).

Refreshing Stale or Unusable Materialized Views

If a materialized view is STALE or UNUSABLE, recompilation alone is not enough—you must refresh the data. The preferred mechanism in modern Oracle releases is the DBMS_MVIEW.REFRESH procedure, which supports FAST, COMPLETE, and FORCE refresh modes.

A typical repair operation for a single materialized view might look like this:


BEGIN
  DBMS_MVIEW.REFRESH(
    list           => 'SALES_SUMMARY_MV',
    method         => 'F',         -- 'F' = FAST, 'C' = COMPLETE, '?' = FORCE
    atomic_refresh => TRUE
  );
END;
/

Guidelines:

Use FAST refresh when materialized view logs and other prerequisites are in place.
Use COMPLETE refresh when logs are missing, when many structural changes have occurred, or when the state is UNUSABLE.
Use FORCE when you want Oracle to attempt a fast refresh and automatically fall back to complete refresh if necessary.

After a successful refresh, re-query USER_MVIEWS to confirm that STALENESS is FRESH.

A Practical Workflow for Troubleshooting Materialized Views

When you suspect a materialized view is not behaving correctly, you can follow this checklist:

Check status
Query USER_MVIEWS (or ALL_MVIEWS/DBA_MVIEWS) to inspect STALENESS, COMPILE_STATE, and LAST_REFRESH_DATE.
Compile if needed
If STALENESS = 'NEEDS_COMPILE', run ALTER MATERIALIZED VIEW ... COMPILE and re-check the state.
Refresh
If the view is STALE or UNUSABLE, use DBMS_MVIEW.REFRESH with the appropriate method (FAST, COMPLETE, or FORCE). For UNUSABLE, a complete refresh is required; in some cases, dropping and recreating the materialized view is the cleanest option.
Validate query rewrite usage
Confirm that QUERY_REWRITE_ENABLED is set appropriately and, if necessary, use DBMS_MVIEW.EXPLAIN_MVIEW and USER_MVIEW_ANALYSIS to diagnose why the optimizer may not be rewriting queries to use the materialized view.

In the next lesson, you will learn how automatic query rewrite uses fresh (and, in some configurations, stale) materialized views to accelerate SQL statements without requiring changes to application code.