[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

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
0
titanium0203
Asked:
titanium0203
1 Solution
 
johanntagleCommented:
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 http://www.experts-exchange.com/Database/Oracle/Q_22760239.html
0
 
OP_ZaharinCommented:
hi titanium,
- you can use either of the following:
: REFRESH FORCE ON DEMAND (by default if not specified)
: REFRESH FORCE ON COMMIT

- check if the view is registered by query to the user_mviews:
SQL> SELECT mview_name, refresh_mode, refresh_method, last_refresh_type, last_refresh_date
           FROM user_mviews;

Open in new window


there are 3 refresh method -  FAST, COMPLETE, or FORCE. you need further understand which is  suitable to your need:
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_6002.htm
0
 
spyresponseCommented:
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 ....
0
 
MujeeburRahmanCommented:
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);
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now