How to Find Stale Materialized Views in Oracle

Use this Oracle Database Script to find Stale Materialized Views in Oracle. What are Stale Materialized Views ? Stale means that using the materialized view would yield a different result than using the base table. You can fix this by:

  • Manually refreshing the view. This is useful when data is written periodically (loaded once an hour).
  • Configuring it for fast refresh on commit. This is useful when data is updated more frequently, and it means that whenever one session commits, the materialized view is updated.
  • Accept stale data (changing the parameter query_rewrite_integrity to STALE_TOLERATED). This is useful when dealing with higher level aggregates.
Any here is the script to identify Stale Materialized Views in Oracle.
column owner heading "Owner" format a10;
column mview_name heading "View Name" format a30;
column last_refresh_date heading "Last Refresh|Date";
column refresh_method heading "Refresh|Method";
select owner,mview_name,last_refresh_date, refresh_method
from all_mview_analysis
where known_stale = 'Y';
clear columns;