How to sync between materialized table and master table?

Posted on 2011-04-24
Last Modified: 2012-05-11

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.

refresh force
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;

Question by:titanium0203
    LVL 24

    Expert Comment

    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

    If on commit refresh is not possible, then use scheduled refresh.  See
    LVL 23

    Accepted Solution

    hi titanium,
    - you can use either of the following:
    : REFRESH FORCE ON DEMAND (by default if not specified)

    - 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:
    LVL 2

    Expert Comment

    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 ....
    LVL 3

    Expert Comment

    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);

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now