We help IT Professionals succeed at work.

How to sync between materialized table and master table?

titanium0203
titanium0203 asked
on
Medium Priority
531 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Top Expert 2012

Commented:
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/Database/Oracle/Q_22760239.html
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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 ....
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);
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.