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

Last updated on AUGUST 27, 2012

Applies to:

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

Symptoms

  1. Several master 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 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.

Changes

 None.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms