1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Create Updatable Materialized View

Discussion in 'Server Administration and Options' started by lovelandj, Mar 16, 2009.

  1. lovelandj

    lovelandj Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    hi everyone,

    how can one create an updateable materialized view? I have heard it can be created through enterprise manager console but i can't figure out how. And how can i set a particular interval for it to get refreshed.

    Also wikipedia says that Advanced Replication option needs to be installed so how do i check if it's installed or not?

    Thanks :)
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hmmm buddy you should use google a little more.. :)

    The following is directly from Oracle documentation:

    You can make a materialized view updatable during creation by including the FOR UPDATE clause or enabling the equivalent option in the Replication Management tool. For changes made to an updatable materialized view to be pushed back to the master during refresh, the updatable materialized view must belong to a materialized view group.

    Updatable materialized views enable you to decrease the load on master sites because users can make changes to the data at the materialized view site. The following is an example of an updatable materialized view:
    Code (Text):

    CREATE MATERIALIZED VIEW hr.departments FOR UPDATE AS
      SELECT * FROM hr.departments@orc1.world;
     
    The following statement creates a materialized view group:
    Code (Text):

    BEGIN
       DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
          gname => 'hr_repg',
          master => 'orc1.world',
          propagation_mode => 'ASYNCHRONOUS');
    END;
    /
     
    The following statement adds the hr.departments materialized view to the materialized view group, making the materialized view updatable:
    Code (Text):

    BEGIN
       DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
          gname => 'hr_repg',
          sname => 'hr',
          oname => 'departments',
          type => 'SNAPSHOT',
          min_communication => TRUE);
    END;

    /