Database DataWarehousing (MOSC)

MOSC Banner

Materialized view missing old records doing fast refresh

edited Jan 8, 2021 9:41PM in Database DataWarehousing (MOSC) 3 commentsAnswered

I have oracle 12.1.0.2 Standard edition

I have 2 materialized view over db link against same table.

CREATE MATERIALIZED VIEW tab1 REFRESH FORCE ON DEMAND WITH PRIMARY KEY AS SELECT * FROM tab@db_link;

CREATE MATERIALIZED VIEW tab2 REFRESH FORCE ON DEMAND WITH PRIMARY KEY AS SELECT * FROM tab@db_link;

The source table as materialized view log

CREATE MATERIALIZED VIEW LOG ON tab WITH PRIMARY KEY INCLUDING NEW VALUES;

We have a job that runs daily and refreshes one of the mviews alternately.

  DBMS_MVIEW.REFRESH (list => tab1, method => '?', atomic_refresh => FALSE);

One of the mivews (tab1) is missing some old data but it does fast refresh so it never gets the old data. If I do complete refresh this issue is resolved.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

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

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center