How to Use Flashback Query to Determine if a Materialized View Was in Sync with Its Master Table When the Refresh Completed (Doc ID 1623808.1)

Last updated on SEPTEMBER 26, 2016

Applies to:

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

Goal

 Occasionally, it may look like DML to a base table was missed and not reflected in the materialized view (mview), but upon the next refresh, what appeared to be "missing" shows up in the mview.  You need to be able to validate that the mview was actually in sync with its master table(s) after the refresh completed. This becomes difficult if there has been DML on the master table(s) after the refresh started.

When a materialized view refresh completes (fast or complete refresh), it should match to the contents of the master table at the time the refresh STARTED.    So, for example, given the following scenario, when the refresh completes, the contents should be in sync with the contents of the master table at 1pm.

  1. You had three rows in the master table at 1:00.
  2. You started a refresh (fast or complete) at 1:00.
  3. At 1:05 another row was inserted into the master table, so it now has four rows.
  4. At 1:10 the refresh completed; the materialized view contained the three rows that existed in the master table when the refresh started.  This is the correct behavior; the row will show up in the mview upon the next refresh.

Keep in mind that if the mview has a predicate, any row in the master table not matching the predicate will not be in the mview; this is also correct behavior.  For example,

  1. You had three rows in the master table at 1:00.  One of these rows had just been updated such that it no longer qualified for inclusion in the mview.
  2. You had the same three rows in the mview at 1:00, but the last update was not reflected in one of the rows yet.
  3. You started a refresh (fast or complete) at 1:00.
  4. At 1:05 you updated the same column/row again in the master table so that it again qualified for inclusion in the mview.
  5. At 1:10, the refresh completed, but it did not include the row that had been updated -- the mview now had only two rows  This is also correct behavior; the row will show up in the mview again upon the next refresh.

 

Solution

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