Re: Anybody studied refresh mechanism of refresh group?

From: Yong Huang <>
Date: Mon, 10 May 2010 09:16:11 -0700 (PDT)
Message-ID: <>


There's one difference between our tests. You have MV log, but my simple test does not:

On remote DB:

create table rmtt1 (x int primary key);
create table rmtt2 (x int primary key);

On local DB:

create materialized view mv1 as select * from yhuang.rmtt1_at_rmtdb; create materialized view mv2 as select * from yhuang.rmtt2_at_rmtdb;

exec dbms_refresh.make('refgroup', 'mv1', sysdate, null, null)
exec dbms_refresh.add('refgroup', 'mv2')
exec dbms_refresh.refresh('refgroup')

So the question is: if I don't have materialized view log, there's no snaptime or snaptime$$ column in any new object. How does Oracle make sure the last refreshed mview in a refresh group only sees the data as of the time the first mview started to be refreshed? A refresh group does not require its mviews to have mview logs.

For what it's worth, I traced the simplest case with mview log without refresh group without two DBs and the snaptime and snaptime$$ don't quite look like what you see. Not sure why.
(Mine is at the bottom of )

Yong Huang

  • On Mon, 5/10/10, amit bansal <> wrote:

Sql should be similar to below (depending on table/log table name)

SELECT /*+ */ "A2"."CO_ID","A2"."TMCODE","A2"."SPCODE","A2"."SNCODE","A2"."CS_SEQNO", ...

Source -

In above query you will see table name CONTR_SERVICES. MLOG$_CONTR_SERVICES is the mview log table. You can see snaptime$$
(time) and DMLTYPE$$ indicating that <> D i.e inserts and updates.
(These are columns in mlog$_<table_name>)

Meanwhile I am not sure why you are not able to find this query in source database.


Received on Mon May 10 2010 - 11:16:11 CDT

Original text of this message