【Best Practices】Creating a Materialized View Replication Environments




  • 1    Introduction to the Materialized View Replication

    物化视图复制包括三种类型:

    1. Read-Only Materialized Views
    2. Updatable Materialized Views
    3. Writeable Materialized Views

    只读物化视图顾名思义就是应用只能读取物化视图的数据,一切DML操作只能在主站点的基表中进行进行。

     1.[ID 20130521.1]

    图示1:只读物化视图复制

    可更新的物化视图,是一种高级的配置,它允许用户插入,更新,删除基表和物化视图中的数据。可更新物化视图基于已建立的支持复制功能的表或其他物化视图。
    可更新物化视图必须是物化视图组的成员。

     2.[ID 20130521.1]

    图示2:可更新物化视图复制

    可写的物化视图就是使用for update选项创建了可更新物化视图,但没有加入物化视图组中。对可写物化视图的DML操作在refresh后,都将丢失无法传回基表。
    因此,创建可更新的物化视图时,如果没有把物化视图加到物化视图组中,则这个物化视图就是可写的物化视图。可写的物化视图,在手动refresh后,写入的数据将丢失。

    2    初始化环境

    在HR用户中创建测试表T并创建T表的主键。

    注:高级复制的表必须要有主键,无法用rowid和伪主键来代替。如果没用主键列,可以考虑使用联合主键来解决。

    [oracle@dbstyle1 ~]$ sqlplus sys/oracle@PROD AS sysdba

    SQL*Plus: RELEASE 10.2.0.1.0 - Production ON Wed May 22 18:31:16 2013
    Copyright (c) 1982, 2005, Oracle.  ALL rights reserved.

    Connected TO:
    Oracle DATABASE 10g Enterprise Edition RELEASE 10.2.0.1.0 - Production
    WITH the Partitioning, Oracle Label Security, OLAP AND Data Mining Scoring Engine options

    SYS@PROD> conn hr/hr@PROD
    Connected.

    HR@PROD> CREATE TABLE t AS SELECT * FROM dba_tables;
    TABLE created.

    HR@PROD> ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (owner,table_name);
    TABLE altered.

    HR@PROD> SELECT object_name,object_type FROM user_objects;

    OBJECT_NAME          OBJECT_TYPE
    -------------------- --------------------
    PK_T                 INDEX
    T                    TABLE

    HR@PROD>

    3    创建可更新物化视图

    要创建物化视图站点环境,需要执行一下步骤:

    1. 创建Master Sites
    2. 创建Master Group
    3. 创建Materialized View Sites
    4. 创建Materialized View group在远程主机

    3.1  创建Master Sites

    创建用于创建Master Sites的脚本setup_masters.sql,并执行:

    /************************* BEGINNING OF SCRIPT ******************************/
    /*
    Setting Up PROD
    Complete the following steps to set up the prod master site.
    Step 1 Connect as SYSTEM at a master site at prod.
    Connect as SYSTEM to the database that you want to set up for replication.
    */


    SET ECHO ON
    SPOOL setup_masters.OUT
    CONNECT SYSTEM/oracle@prod

    /*
    Step 2 Create the replication administrator at prod.
    The replication administrator must be granted the necessary privileges to create and
    manage a replication environment. The replication administrator must be created at
    each database that participates in the replication environment.
    */


    CREATE USER repadmin IDENTIFIED BY repadmin;

    /*
    Step 3 Grant privileges to the replication administrator at prod.
    Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant the replication
    administrator powerful privileges to create and manage a replicated environment.
    */


    BEGIN
    DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
    username => 'repadmin');
    END;
    /

    /*
    If you want your repadmin to be able to create materialized view logs for any
    replicated table, then grant COMMENT ANY TABLE and LOCK ANY TABLE to repadmin:
    */


    GRANT COMMENT ANY TABLE TO repadmin;
    GRANT LOCK ANY TABLE TO repadmin;

    /*
    If you want your repadmin to be able to connect to the Replication Management tool,
    then grant SELECT ANY DICTIONARY to repadmin:
    */


    GRANT SELECT ANY DICTIONARY TO repadmin;

    /*
    Step 4 Register the propagator at prod.
    The propagator is responsible for propagating the deferred transaction queue to other
    master sites.
    */


    BEGIN
    DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
    username => 'repadmin');
    END;
    /

    /*
    Step 5 Register the receiver at prod.
    The receiver receives the propagated deferred transactions sent by the propagator
    from other master sites.
    */


    BEGIN
    DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
    username => 'repadmin',
    privilege_type => 'receiver',
    list_of_gnames => NULL);
    END;
    /

    /*
    Step 6 Schedule purge at master site prod.
    In order to keep the size of the deferred transaction queue in check, you should purge
    successfully completed deferred transactions. The SCHEDULE_PURGE procedure
    automates the purge process for you. You must execute this procedure as the
    replication administrator.
    */


    CONNECT repadmin/repadmin@prod
    BEGIN
    DBMS_DEFER_SYS.SCHEDULE_PURGE (
    next_date => SYSDATE,
    INTERVAL => 'SYSDATE + 1/24',
    delay_seconds => 0);
    END;
    /

    /*
    Step 7 Create proxy master site users at prod.
    If you plan to create materialized view sites based on this master site, then create
    proxy master site users at prod that correspond to users at the materialized
    view site.
    Create the proxy materialized view administrator.
    The proxy materialized view administrator performs tasks at the target master site on
    behalf of the materialized view administrator at the materialized view site.
    */


    CONNECT SYSTEM/oracle@prod
    CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;
    BEGIN
    DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
    username => 'proxy_mviewadmin',
    privilege_type => 'proxy_snapadmin',
    list_of_gnames => NULL);
    END;
    /

    -- Place GRANT SELECT_CATALOG_ROLE statement here if necessary.
    /*
    If you want your materialized view administrator at materialized view sites to be able
    to perform administrative operations using the Replication Management tool, then
    grant SELECT_CATALOG_ROLE to proxy_mviewadmin:
    GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;
    Granting this privilege to the proxy_mviewadmin is not required if you do not plan
    to use the Replication Management tool. However, if you plan to use the Replication
    Management tool, then move the GRANT statement to the line directly after the
    previous REGISTER_USER_REPGROUP statement.
    Create the proxy refresher.
    The proxy refresher performs tasks at the master site on behalf of the refresher at the
    materialized view site.
    */


    CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;
    GRANT CREATE SESSION TO proxy_refresher;
    GRANT SELECT ANY TABLE TO proxy_refresher;
    SET ECHO OFF
    SPOOL OFF
    /**************************END OF SCRIPT***********************************/

    3.2  创建Master Group

    创建create_mg.sql,并执行:

    /************************* BEGINNING OF SCRIPT ******************************/

    SET ECHO ON
    SPOOL create_mg.OUT
    CONNECT repadmin/repadmin@prod

    /*
    Step 1 Create the schema at master sites.
    If the schema does not already exist at all of the master sites participating in the master
    group, then create the schema now and grant it all of the necessary privileges. This
    example uses the hr schema, which is one of the sample schemas that are installed by
    default when you install Oracle. So, the hr schema should exist at all master sites.
    */


    PAUSE Press <RETURN> TO continue WHEN the SCHEMA EXISTS AT ALL master sites.

    /*
    Step 2 Create the master group.
    Use the CREATE_MASTER_REPGROUP procedure to define a new master group. When
    you add an object to your master group or perform other replication administrative
    tasks, you reference the master group name defined during this step. This step must be
    completed by the replication administrator.
    */


    BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPGROUP (
    gname => 'hr_repg');
    END;
    /

    /*
    Step 3 Add objects to master group.
    Use the CREATE_MASTER_REPOBJECT procedure to add an object to your master
    group. In most cases, you probably will be adding tables and indexes to your master
    group, but you can also add procedures, views, synonyms, and so on.
    */


    BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
    gname => 'hr_repg',
    TYPE => 'TABLE',
    oname => 't',
    sname => 'hr',
    use_existing_object => TRUE,
    copy_rows => FALSE);
    END;
    /

    /*
    Only one master sites,this step is pass.
    Step 4 Add additional master sites.
    After you have defined your master group at the master definition site (the site where
    the master group was created becomes the master definition site by default), you can
    define the other sites that will participate in the replication environment. You might
    have guessed that you will be adding the orc2.world and orc3.world sites to the
    replication environment. This example creates the master group at all master sites, but
    you have the option of creating the master group at one master site now and adding
    additional master sites later without quiescing the database. In this case, you can skip
    this step.
    In this example, the use_existing_objects parameter in the ADD_MASTER_
    DATABASE procedure is set to TRUE because it is assumed that the hr schema already
    exists at all master sites. In other words, it is assumed that the objects in the hr schema
    are precreated at all master sites. Also, the copy_rows parameter is set to FALSE
    because it is assumed that the identical data is stored in the tables at each master site.
    */


    /*
    Step 5 If conflicts are possible, then configure conflict resolution methods.
    */


    PAUSE Press <RETURN> TO continue after configuring conflict resolution methods OR IF no conflict resolution methods are required.

    /*
    Step 6 Generate replication support.
    */


    BEGIN
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
    sname => 'hr',
    oname => 't',
    TYPE => 'TABLE',
    min_communication => TRUE);
    END;
    /

    PAUSE Press <RETURN> TO continue.

    /*
    Step 7 Start replication.
    After creating your master group, adding replication objects, generating replication
    support, and adding additional master databases, you need to start replication activity.
    Use the RESUME_MASTER_ACTIVITY procedure to "turn on" replication for the
    specified master group.
    */


    BEGIN
    DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
    gname => 'hr_repg');
    END;
    /
    SET ECHO OFF
    SPOOL OFF

    /************************* END OF SCRIPT **********************************/

    3.3  创建Materialized View Sites

    创建setup_mvs.sql,并执行:

    /************************* BEGINNING OF SCRIPT ******************************/
    /*
    Step 1 Connect as SYSTEM at materialized view site at emrep.
    You must connect as SYSTEM to the database that you want to set up as a materialized
    view site.
    */


    SET ECHO ON
    SPOOL setup_mvs.OUT
    CONNECT SYSTEM/oracle@emrep

    /*
    Step 2 Create materialized view site users at emrep.
    Several users must be created at the materialized view site. These users are:
    Materialized view administrator
    Propagator
    Refresher
    Receiver (if the site will serve as a master materialized view site for other
    materialized views, as emrep is)
    Complete the following tasks to create these users.
    Create the materialized view administrator.
    The materialized view administrator is responsible for creating and managing the
    materialized view site. Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant
    the materialized view administrator the appropriate privileges.
    */


    CREATE USER mviewadmin IDENTIFIED BY mviewadmin;
    BEGIN
    DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
    username => 'mviewadmin');
    END;
    /
    GRANT COMMENT ANY TABLE TO mviewadmin;
    GRANT LOCK ANY TABLE TO mviewadmin;

    /*
    If you want your mviewadmin to be able to connect to the Replication Management
    tool, then grant SELECT ANY DICTIONARY to mviewadmin:
    */


    GRANT SELECT ANY DICTIONARY TO mviewadmin;

    /*
    Create the propagator.
    The propagator is responsible for propagating the deferred transaction queue to the
    target master site.
    */


    CREATE USER propagator IDENTIFIED BY propagator;
    BEGIN
    DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
    username => 'propagator');
    END;
    /

    /*
    Create the refresher.
    The refresher is responsible for "pulling" changes made to the replicated tables at the
    target master site to the materialized view site. This user refreshes one or more
    materialized views. If you want the mviewadmin user to be the refresher, then this
    step is not required.
    */


    CREATE USER refresher IDENTIFIED BY refresher;
    GRANT CREATE SESSION TO refresher;
    GRANT ALTER ANY MATERIALIZED VIEW TO refresher;

    /*
    Register the receiver.
    The receiver receives the propagated deferred transactions sent by the propagator
    from materialized view sites. The receiver is necessary only if the site will function as a
    master materialized view site for other materialized view sites.
    */


    BEGIN
    DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
    username => 'mviewadmin',
    privilege_type => 'receiver',
    list_of_gnames => NULL);
    END;
    /

    /*
    Step 3 Create database links to the master site.
    Create a public database link.

    */


    CONNECT SYSTEM/oracle@emrep
    CREATE PUBLIC DATABASE LINK prod USING 'PROD';

    /*
    Create the materialized view administrator database link.
    You need to create a database link from the materialized view administrator at the
    materialized view site to the proxy materialized view administrator at the master site.
    */


    CONNECT mviewadmin/mviewadmin@emrep;
    CREATE DATABASE LINK prod
    CONNECT TO proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

    /*
    Create the propagator/receiver database link.
    You need to create a database link from the propagator at the materialized view site to
    the receiver at the master site. The receiver was defined when you created the master
    site.
    */


    CONNECT propagator/propagator@emrep
    CREATE DATABASE LINK prod
    CONNECT TO repadmin IDENTIFIED BY repadmin;

    /*
    Step 4 Schedule purge at the emrep materialized view site.
    In order to keep the size of the deferred transaction queue in check, you should purge
    successfully completed deferred transactions. The SCHEDULE_PURGE procedure
    automates the purge process for you. If your materialized view site only contains
    "read-only" materialized views, then you do not need to execute this procedure.
    */


    CONNECT mviewadmin/mviewadmin@emrep
    BEGIN
    DBMS_DEFER_SYS.SCHEDULE_PURGE (
    next_date => SYSDATE,
    INTERVAL => 'SYSDATE + 1/24',
    delay_seconds => 0,
    rollback_segment => '');
    END;
    /

    /*
    Step 5 Schedule push at the emrep materialized view site (optional).
    If the materialized view site has a constant connection to its master site, then you
    optionally can schedule push at the emrep materialized view site. If the
    materialized view site is disconnected from its master site for extended periods of
    time, then it is typically better not to schedule push and refresh on demand, which
    pushes changes to the master site.
    The SCHEDULE_PUSH procedure schedules when the deferred transaction queue
    should be propagated to the target master site.
    */


    CONNECT mviewadmin/mviewadmin@emrep
    BEGIN
    DBMS_DEFER_SYS.SCHEDULE_PUSH (
    destination => 'prod',
    INTERVAL => 'SYSDATE + 1/24',
    next_date => SYSDATE,
    stop_on_error => FALSE,
    delay_seconds => 0,
    parallelism => 0);
    END;
    /

    /*
    Step 6 Create proxy users at the emrep materialized view site.
    Create the proxy materialized view administrator.
    The proxy materialized view administrator performs tasks at the target master
    materialized view site on behalf of the materialized view administrator at the
    materialized view sites based on this materialized view site. This user is not required if
    the site will not function as a master materialized view site for other materialized view
    sites.
    */


    CONNECT SYSTEM/oracle@emrep
    CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;
    BEGIN
    DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
    username => 'proxy_mviewadmin',
    privilege_type => 'proxy_snapadmin',
    list_of_gnames => NULL);
    END;
    /

    -- Place GRANT SELECT_CATALOG_ROLE statement here if necessary.

    /*
    If you want your materialized view administrator at materialized view sites based on
    this materialized view site to be able to perform administrative operations using the
    Replication Management tool, then grant SELECT_CATALOG_ROLE to proxy_
    mviewadmin:
    GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;
    Granting this privilege to the proxy_mviewadmin is not required if you do not plan
    to use the Replication Management tool. However, if you plan to use the Replication
    Management tool, then move the GRANT statement to the line directly after the
    previous REGISTER_USER_REPGROUP statement.
    Create the proxy refresher.
    The proxy refresher performs tasks at the master materialized view site on behalf of
    the refresher at the materialized view sites based on this materialized view site. This
    user is not required if the site will not function as a master materialized view site for
    other materialized view sites.
    */


    CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;
    GRANT CREATE SESSION TO proxy_refresher;
    GRANT SELECT ANY TABLE TO proxy_refresher;
    SET ECHO OFF
    SPOOL OFF
    /************************* END OF SCRIPT **********************************/

    3.4  创建Materialized View group在远程主机

    创建create_mv_group.sql,并执行

    /************************* BEGINNING OF SCRIPT ******************************/
    /*
    Creating the Materialized View Group at emrep
    Complete the following steps to create the hr_repg materialized view group at the
    emrep materialized view site. This materialized view group is based on the hr_
    repg master group at the orc1.world master site.
    Step 1 Create materialized view logs at the master site.
    If you want one of your master sites to support a materialized view site, then you need
    to create materialized view logs for each master table that is replicated to a
    materialized view. Recall from Figure 2–1 on page 2-2 that orc1.world serves as the
    target master site for the emrep materialized view site. The required materialized
    view logs must be created at orc1.world.
    */


    SET ECHO ON
    SPOOL create_mv_group.OUT
    CONNECT hr/hr@prod
    CREATE MATERIALIZED VIEW LOG ON hr.t;

    /*
    Step 2 If they do not already exist, then create the replicated schema and its
    database link.
    Before building your materialized view group, you must make sure that the replicated
    schema exists at the remote materialized view site and that the necessary database
    links have been created.
    In this example, if the hr schema does not exist, then create the schema. If the hr
    schema already exists at the materialized view site, then grant any necessary privileges
    and go to the next task in this step.
    */


    CONNECT SYSTEM/oracle@emrep
    CREATE TABLESPACE demo_mv1
    DATAFILE 'demo_mv1.dbf' SIZE 10M AUTOEXTEND ON
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
    CREATE TEMPORARY TABLESPACE temp_mv1
    TEMPFILE 'temp_mv1.dbf' SIZE 5M AUTOEXTEND ON;
    CREATE USER hr IDENTIFIED BY hr;
    ALTER USER hr DEFAULT TABLESPACE demo_mv1
    QUOTA UNLIMITED ON demo_mv1;
    ALTER USER hr TEMPORARY TABLESPACE temp_mv1;

    GRANT
    CREATE SESSION,
    CREATE TABLE,
    CREATE PROCEDURE,
    CREATE SEQUENCE,
    CREATE TRIGGER,
    CREATE VIEW,
    CREATE SYNONYM,
    ALTER SESSION,
    CREATE MATERIALIZED VIEW,
    ALTER ANY MATERIALIZED VIEW,
    CREATE DATABASE LINK
    TO hr;

    /*
    If it does not already exist, then create the database link for the replicated schema.
    Before building your materialized view group, you must make sure that the necessary
    database links exist for the replicated schema. The owner of the materialized views
    needs a database link pointing to the proxy_refresher that was created when the
    master site was set up.
    */


    CONNECT hr/hr@emrep
    CREATE DATABASE LINK prod
    CONNECT TO proxy_refresher IDENTIFIED BY proxy_refresher;

    /*
    Step 3 Create the materialized view group.
    The following procedures must be executed by the materialized view administrator at
    the remote materialized view site.
    */


    CONNECT mviewadmin/mviewadmin@emrep

    /*
    The master group that you specify in the gname parameter must match the name of
    the master group that you are replicating at the target master site.
    */


    BEGIN
    DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
    gname => 'hr_repg',
    master => 'prod',
    propagation_mode => 'ASYNCHRONOUS');
    END;
    /

    /*
    Step 4 Create the refresh group.
    All materialized views that are added to a particular refresh group are refreshed at the
    same time. This ensures transactional consistency between the related materialized
    views in the refresh group.
    */


    BEGIN
    DBMS_REFRESH.MAKE (
    name => 'mviewadmin.hr_refg',
    list => '',
    next_date => SYSDATE,
    INTERVAL => 'SYSDATE + 1/24',
    implicit_destroy => FALSE,
    rollback_seg => '',
    push_deferred_rpc => TRUE,
    refresh_after_errors => FALSE);
    END;
    /

    /*
    Step 5 Add objects to the materialized view group.
    Create the materialized views based on the master tables.
    Whenever you create a materialized view, always specify the schema name of the table
    owner in the query for the materialized view. In the following examples, hr is
    specified as the owner of the table in each query.
    */


    CREATE MATERIALIZED VIEW hr.t_mv1
    REFRESH FAST WITH PRIMARY KEY FOR UPDATE
    AS SELECT * FROM hr.t@prod;

    /*
    Add the objects to the materialized view group.
    */


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

    /*
    Step 6 Add objects to the refresh group.
    All of the materialized view group objects that you add to the refresh group are
    refreshed at the same time to preserve referential integrity between related
    materialized views.
    */


    BEGIN
    DBMS_REFRESH.ADD (
    name => 'mviewadmin.hr_refg',
    list => 'hr.t_mv1',
    lax => TRUE);
    END;
    /

    SET ECHO OFF
    SPOOL OFF

    /************************* END OF SCRIPT **********************************/

    4    验证

    4.1  向基表中增加数据

    [oracle@dbstyle1 ~]$ sqlplus hr/hr@PROD

    SQL*Plus: RELEASE 10.2.0.1.0 - Production ON Wed May 22 19:42:26 2013
    Copyright (c) 1982, 2005, Oracle.  ALL rights reserved.

    Connected TO:
    Oracle DATABASE 10g Enterprise Edition RELEASE 10.2.0.1.0 - Production
    WITH the Partitioning, Oracle Label Security, OLAP AND Data Mining Scoring Engine options

    HR@PROD> SELECT * FROM tab;

    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    T                              TABLE
    MLOG$_T                        TABLE
    RUPD$_T                        TABLE

    HR@PROD> INSERT INTO t(owner,table_name) VALUES('test','test');

    1 ROW created.

    HR@PROD> SELECT * FROM mlog$_t;

    OWNER      TABLE_NAME       SNAPTIME$ D O CHANGE_VECTOR$$
    ---------- ---------------- --------- - - ----------------
    test       test             01-JAN-00 I N FEFFFFFFFFFFFF

    HR@PROD> COMMIT;

    COMMIT complete.

    HR@PROD> SELECT * FROM t WHERE owner='test1' AND table_name='test1';

    no ROWS selected

    4.2  向物化视图中插入数据

    [oracle@dbstyle1 ~]$ sqlplus hr/hr@EMREP

    SQL*Plus: RELEASE 10.2.0.1.0 - Production ON Wed May 22 22:10:53 2013
    Copyright (c) 1982, 2005, Oracle.  ALL rights reserved.

    Connected TO:
    Oracle DATABASE 10g Enterprise Edition RELEASE 10.2.0.1.0 - Production
    WITH the Partitioning, Oracle Label Security, OLAP AND Data Mining Scoring Engine options

    HR@EMREP> SELECT * FROM tab;

    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    T_MV1                          TABLE
    USLOG$_T_MV1                   TABLE

    HR@EMREP> SELECT * FROM t_mv1 WHERE owner='test' AND table_name='test';

    no ROWS selected

    HR@EMREP> INSERT INTO t_mv1(owner,table_name) VALUES('test1','test1');

    1 ROW created.

    HR@EMREP> COMMIT;

    COMMIT complete.

    4.3  刷新物化视图组

    HR@EMREP> conn mviewadmin/mviewadmin@EMREP

    Connected.

    MVIEWADMIN@EMREP> EXEC dbms_refresh.refresh('hr_refg');

    PL/SQL PROCEDURE successfully completed.

    4.4  查看数据是否刷新

    查看EMREP

    HR@emrep> SELECT owner,table_name FROM t_mv1 WHERE owner='test' AND table_name='test';

    OWNER                          TABLE_NAME
    ------------------------------ ------------------------------
    test                           test

    查看PROD

    HR@PROD> SELECT * FROM mlog$_t;

    no ROWS selected

    HR@PROD> SELECT owner,table_name FROM t WHERE owner='test1' AND table_name='test1';

    OWNER                          TABLE_NAME
    ------------------------------ ------------------------------
    test1                          test1

    5    补充方法

    删除repobject

    BEGIN
    DBMS_REPCAT.DROP_MASTER_REPOBJECT (
    TYPE => 'TABLE',
    oname => 't',
    sname => 'hr');
    END;
    /

    删除repgroup

    BEGIN
    DBMS_REPCAT.drop_MASTER_REPGROUP (
    gname => 'hr_repg');
    END;
    /

    刷新hr_refg组方法:

    conn mviewadmin/mviewadmin@emrep
    EXEC dbms_refresh.refresh('hr_refg');

    参考手册

    详细说明请参考官方文档:

    《Advanced Replication Management API Reference》

    《Advanced Replication》

    Comments

    1. Very Good.

      Good luck.

      secooler
      13.05.28

      -- The End --

    2. Hi,
      I want to know the difference between the replication env. and the common mv .
      It seems very complex ,but I thinks there may be any advantage.
      Waiting for your reply.
      Many Thanks.
      Ingram
      2013-06-01

      • I understand that:
        Common materialized view is used for the data warehouse, it is more used to improve the retrieval of data warehouse efficiency, and basically read-only.
        The replication environments mv is based on the principle of materialized views to achieve replication, More use of Updatable materialized views(because you could update the base table).

    3. Good post. I certainly love this site. Thanks!

    Speak Your Mind

    *