In a development DB where most MView not refreshed, USER_MVIEW.STALE_SINCE were changing slowly. This makes no sense to me because once it became stale, the time when it stale should be fixed, until something made it no longer stale. Are STALENESS and STALE_SINCE total mess?
Some observation:
All MView has same STALE_SINCE (if any) regardless of STALENESS .
Some FRESH MViews have non-null STALE_SINCE
All STALE_SINCE were nearly 6 days ago. Is this the maximum staleness?
Same phenomenon with DBA_MVIEWS and ALL_MVIEWS .
select sysdate, staleness, min(stale_since), max(stale_since), max(last_refresh_date), count(*) from user_mviews
group by staleness;
SYSDATE STALENESS MIN(STALE_SINCE) MAX(STALE_SINCE) COUNT(*)
------------------- ------------------- ------------------- ------------------- ----------
2022-01-25 16:58:05 UNDEFINED 2022-01-19 18:29:38 2022-01-19 18:29:38 4
2022-01-25 16:58:05 FRESH 2022-01-19 18:29:38 2022-01-19 18:29:38 10
2022-01-25 16:58:05 COMPILATION_ERROR 4
2022-01-25 16:58:05 NEEDS_COMPILE 2022-01-19 18:29:38 2022-01-19 18:29:38 16
2022-01-25 16:58:05 UNKNOWN 2022-01-19 18:29:38 2022-01-19 18:29:38 1
(ran again a few minues later)
SYSDATE STALENESS MIN(STALE_SINCE) MAX(STALE_SINCE) COUNT(*)
------------------- ------------------- ------------------- ------------------- ----------
2022-01-25 17:02:32 UNDEFINED 2022-01-19 18:33:56 2022-01-19 18:33:56 4
2022-01-25 17:02:32 FRESH 2022-01-19 18:33:56 2022-01-19 18:33:56 10
2022-01-25 17:02:32 COMPILATION_ERROR 4
2022-01-25 17:02:32 NEEDS_COMPILE 2022-01-19 18:33:56 2022-01-19 18:33:56 16
2022-01-25 17:02:32 UNKNOWN 2022-01-19 18:33:56 2022-01-19 18:33:56 1
select last_refresh_type, last_refresh_date, staleness, compile_state, stale_since from user_mviews
where staleness='FRESH' and stale_since is not null;
LAST_REF LAST_REFRESH_DATE STALENESS COMPILE_STATE STALE_SINCE
-------- ------------------- ------------------- ------------------- -------------------
COMPLETE 2022-01-10 17:39:20 FRESH VALID 2022-01-19 18:43:56
COMPLETE 2022-01-10 17:42:53 FRESH VALID 2022-01-19 18:43:56
COMPLETE 2022-01-10 17:38:19 FRESH VALID 2022-01-19 18:43:56
COMPLETE 2022-01-10 17:42:53 FRESH VALID 2022-01-19 18:43:56
COMPLETE 2022-01-10 17:38:11 FRESH VALID 2022-01-19 18:43:56
COMPLETE 2022-01-10 17:39:01 FRESH VALID 2022-01-19 18:43:56
All MViews were based on quite complex SQL, such that they were not fast-refreshable, and query rewrite not enabled.
19.8 on Linux