Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: replicating snapshots (multitiered materialized views)..

Re: replicating snapshots (multitiered materialized views)..

From: Anurag Varma <>
Date: 11 Feb 2005 09:58:14 -0800
Message-ID: <>

Did you setup the mviews as following?

site A:

     create materialized view a_mview
     as select ... from table1 where;

     create materialized view log on a_mview;

site B:
     create materialized view b_mview
     refresh fast
     as select * from a_mview_at_siteA;

? Then that would count as a nested mview. To me it appears that you have the setup a little different?

The error you are getting states that the mview log of the master has been purged. As a result, the necessary
data required to fast refresh the slave is not there. Now how would the data in mview log of master get purged? 1. If you create the b_mview *before* you create the mview log on a_mview, then you would get the error.
2. When an mview is created, the mview is first registered at the master site (look in dba_registered_mviews).

    This then helps oracle to decide whether to purge a mview log. If all the registered mviews have been refreshed,

    then oracle knows that its safe to purge the mview log since all the registered mviews have been refreshed.

    The registering is usually done automatically when you create a mview .. or when you do a complete refresh

    of the mview.
    Consider a case when there is mview log on a_mview, and there are two mviews defined on a_mview (called

    b_mview and c_mview)
    In case the registering has failed for b_mview, its possible that c_mview defined on the master (a_mview)

    is the only mview registered. And when c_mview refreshes against the master (a_mview)

    then oracle purges the mview log (of a_mview), before b_mview gets a chance to refresh. Thus as a result,

    b_mview gets an error stating that the materialized view log on a_mview is younger than the last refresh.

    The solution to this would be to recreate the b_mview or do a complete refresh .. and make sure that

     b_mview is registered in the master site (dba_registered_mviews).

Hope that is a better explanation. You might want to specify exactly how you set up these mviews / mview log's in case it still does not work for you.

Anurag Received on Fri Feb 11 2005 - 11:58:14 CST

Original text of this message