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

Last updated on NOVEMBER 07, 2023

Applies to:

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 base table(s) after the refresh completed. This becomes difficult if there has been DML on the base table(s) after the refresh started.

When a materialized view refresh completes (fast or complete refresh), it should match to the contents of the base 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 base table at 1pm.

1. You had three rows in the base 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 base 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 base 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 base 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 base 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 base 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

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