3 Replies Latest reply: Jan 28, 2013 6:39 AM by Nuno RSS

    Updatable materialized views

    Nuno
      Environment: DB is Oracle 11g

      I wanted to replicate part of my database in a offline laptop, using Materialized views (and refreshing them when the laptop would be online with the master DB).
      So far, so good.

      Step 2 was to allow some of those views to be changeable. So, I decided to use updatable MVs.

      Question1: to create an updatable view, the master table must have a primary key?
      Question2: the updatable command overrides the fast refresh, or they can (should) be used together?

      I created the MV Log in the master

      then the refresh group in remote

      BEGIN
      DBMS_REFRESH.MAKE ( name => 'my_group',
      list => '',
      next_date => '' , interval => '',
      implicit_destroy => FALSE, rollback_seg => '',
      push_deferred_rpc => TRUE, refresh_after_errors => FALSE);
      END;

      CREATE MATERIALIZED VIEW "TARGET_ACTIVITY" ("ID", "FAO_CODE", "NAME", "PARENT_ID") BUILD IMMEDIATE
      REFRESH FORCE ON DEMAND NEXT NULL
      USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS FOR UPDATE DISABLE QUERY REWRITE AS
      SELECT "TARGET_ACTIVITY"."ID" "ID",
      "TARGET_ACTIVITY"."FAO_CODE" "FAO_CODE",
      "TARGET_ACTIVITY"."NAME" "NAME",
      "TARGET_ACTIVITY"."PARENT_ID" "PARENT_ID"
      FROM "TARGET_ACTIVITY"@"SERVIDOR" "TARGET_ACTIVITY";

      Added view to refresh group

      BEGIN
      DBMS_REFRESH.add( name => 'my_group', list => 'TARGET_ACTIVITY', lax => TRUE);
      END;


      But whenever I refresh the group, the updates in the remote table are deleted and they do not update the master... shouldn't they pass to the master table AND stay in the remote table? Or did I get this updatable thing all wrong?


      Thanks for any help
        • 1. Re: Updatable materialized views
          767217
          Did you think about using import-export utility for this like imp/exp or impdp/expdp (could be done thought network - NETWORK_LINK)

          Or you could use Database Advanced Replication http://docs.oracle.com/cd/B19306_01/server.102/b14226/toc.htm
          for example Updatable Materialized Views http://docs.oracle.com/cd/B19306_01/server.102/b14226/repmview.htm#sthref396
          • 2. Re: Updatable materialized views
            767217
            http://apunhiran.blogspot.com/2009/04/how-to-setup-multi-masteradvanced.html
            • 3. Re: Updatable materialized views
              Nuno
              As I read, my mistake is not putting the MV in a MV group. I have in a REFRESH_GROUP, which is a different concept?(???)

              Updatable materialized views have the following properties.

              They are always based on a single table, although multiple tables can be referenced in a subquery.

              They can be incrementally (or fast) refreshed.

              Oracle propagates the changes made to an updatable materialized view to the materialized view's remote master table or master materialized view. The updates to the master then cascade to all other replication sites.

              I should create a MV group, and add it to the refresh group??

              PS: I just realized there is a REPLICATION forum... is there a way to pass this thread there, or do I need to create a new one?