1 Reply Latest reply: Jan 28, 2013 1:41 PM by spajdy 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.
      I assumed that just creating an updatable materialized view and adding it to a refresh group would do the trick. SO...

      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 ON DEMAND FAST 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;

      Whenever I change a record in the replicated table, I see that change in the Log associated with it.
      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?

      I also read that I would need to add it to a MV GROUP (not refresh group), but I got the ideia that was only for multimaster scenarios (which is not the case). I'm a bit confused here, since I only used read only MVs...


      Thanks for any help
        • 1. Re: Updatable materialized views
          spajdy
          So you want to use Master-Snapshot replication with update-able snapshot.
          In general you have to:
          1/ on master site:
          * user who act in role replication administrator
          * create master replication group
          * create master replication object in this group (this master object is build on master table)
          * define conflict resolution methods if necessary
          * generate replication support (set of PL/SQL packages)
          * resume master replication group
          2/ on snapshot site
          * user who act in role replication administrator
          * create DB link to master DB
          * create snapshot group
          * create update-able snapshot in this group
          * create refresh group
          * snapshot to refresh group
          * create job to push changes done in snapshot site to master (deferred transactions, replication is done using AQ)

          For details see documentation or search this forum.