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.

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;