My Oracle Support Banner

Last Refresh Time Keeps Gaining More Than Wall Clock Time (Doc ID 1485730.1)

Last updated on AUGUST 26, 2021

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.


NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.


  1. Several primary sites all have the correct system day/time, but the last refresh dates of some mviews are days in the future. The difference between real time and the last refresh time continues to grow.
  2. Dropping and recreating the affected mviews sets the time back to normal; then the last-refresh times start growing faster than real clock time again.
  3. The mviews appear to have the correct data. However, these databases need to be migrated/upgraded and there is concern about whether or not this will cause a problem with the fast refresh after migration.
  4. On all materialized view sites for the affected mviews, sys.sum$.lastrefreshdate and sys.snap_reftime$.snaptime are different. The value for snap_reftime$.snaptime is almost a few weeks in the future.
  5. Dictionary views pertaining to these two base tables also report different dates. For example, DBA_SNAPSHOTS and DBA_MVIEWS have different dates for last refresh. Similarly DBA_MVIEWS and DBA_MVIEW_REFRESH_TIMES are showning different dates, as they pull from sum$ and snap_reftime$, respectively.




To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.