asked on

How to sync between materialized table and master table?

Hi,

How to update table below to sync with the master table? Any updates in master table will reflect according in clone_table, I tried "refresh force" but looks like it doesn't work.

CREATE MATERIALIZED VIEW
clone_table
refresh force
as
select product_id,retailer_id,sum(item_price) sales,sum(case when ITEM_PRICE >= 0 then QTY_ORDERED else 0 end) qtyo,sum(case when ITEM_PRICE < 0 and QTY_ORDERED > 0 then QTY_ORDERED else 0 end) qtyd,trunc(TXN_DATE) as operdate
from sale_transactions;

Regards,
titanium

johanntagle

It should be "refresh fast on commit"

However, you need to check if Oracle will allow it as there are certain conditions that have to be met for on commit refresh to be allowed. See http://www.sqlsnippets.com/en/topic-12894.html

If on commit refresh is not possible, then use scheduled refresh. See https://www.experts-exchange.com/questions/22760239/How-to-refresh-a-materialized-view-in-scheduled-time-like-4-00-AM.html

ASKER CERTIFIED SOLUTION

OP_Zaharin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

spyresponse

steps for creating materialized view ..... using refresh fast on commit they effect on after the commit your transaction....

before create materialized view .....u need to create materialized log....

create materialized view log on <table_name>;

after log ......

1create materialized view <name_materialized view>
2 build immediate
3 refresh fast on commit
4 as
5 select * from <table_name>;

if any other error on this send me snap shot ....

MujeeburRahman

1.create MV log on master table at master site.
2. Create Master group at master site.
3. Add the table to the master group at master site
4. Create MV table at MV site.
5. Create MV group at MV site.
6. Add MV table to the MV group at MV site.
To make refresh connect to the MV table owner schema and execute the following
execute dbms_snapshot.refresh('MVtablename','F','',TRUE,TRUE);