建立可更新的物化视图

时间：2010-06-18 来源：rushmeplz

环境 RAC和单实例建立可更新的物化视图 SQL> select * from gv$version;    INST_ID BANNER
---------- ----------------------------------------------------------------
         2 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
         2 PL/SQL Release 10.2.0.4.0 - Production
         2 CORE 10.2.0.4.0      Production
         2 TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
         2 NLSRTL Version 10.2.0.4.0 - Production
         1 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
         1 PL/SQL Release 10.2.0.4.0 - Production
         1 CORE 10.2.0.4.0      Production
         1 TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
         1 NLSRTL Version 10.2.0.4.0 - Production SQL> select * from v$version; BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production 1.配置复制管理用户repadmin(所有节点) create user repadmin identified by repadmin; execute dbms_defer_sys.register_propagator('repadmin');
grant execute any procedure to repadmin;
execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
execute dbms_repcat_admin.grant_admin_any_schema(username => '"REPADMIN"');
grant comment any table to repadmin;
grant lock any table to repadmin;
grant select any dictionary to repadmin; 2.启用global_name(所有节点) alter system set global_names=true scope=spfile; alter system set db_domain='lab.com' scope=spfile; alter database rename global_name to gc.lab.com; alter database rename global_name to orcl.lab.com; select * from global_name; 3.建立dblink(所有节点) ORCL上 CREATE PUBLIC DATABASE LINK "GC.LAB.COM"
CONNECT TO SYSTEM
IDENTIFIED BY <PWD>
USING 'GC'; GC上 CREATE PUBLIC DATABASE LINK "ORCL.LAB.COM"
CONNECT TO SYSTEM
IDENTIFIED BY <PWD>
USING 'orcl1'; 通过select * from global_name@dblinkname来测试创建私有database link,用于拉起和停止节点repgroup的状态 repadmin下 ORCL CREATE DATABASE LINK "GC.LAB.COM"
CONNECT TO REPADMIN
IDENTIFIED BY <PWD>; GC CREATE DATABASE LINK "ORCL.LAB.COM"
CONNECT TO REPADMIN
IDENTIFIED BY <PWD>; 通过select * from global_name@dblinkname来测试 4.源表上建立materialized view log(ORCL) CREATE MATERIALIZED VIEW LOG ON TEST.BA_ALCCLS
NOCACHE
LOGGING
NOPARALLEL
WITH PRIMARY KEY; 建立复制组 SQL> exec dbms_repcat.create_master_repgroup('mv');
把源表添加到复制组中 SQL> exec dbms_repcat.create_master_repobject('test','ba_alccls','table',gname =>'mv'); 为源表添加复制支持 SQL> exec dbms_repcat.generate_replication_support('test','ba_alccls','table'); 激活复制组 SQL> exec dbms_repcat.resume_master_activity('mv'); 5.在物化视图站点 CREATE MATERIALIZED VIEW TEST.BA_ALCCLS
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
FOR UPDATE
AS
SELECT "BA_ALCCLS"."ALCZX" "ALCZX", "BA_ALCCLS"."ALCLB" "ALCLB"
FROM "TEST"."BA_ALCCLS"@orcl.lab.com "BA_ALCCLS"; 建立物化视图复制组 SQL> exec dbms_repcat.create_mview_repgroup('mv','orcl.lab.com'); 建立物化视图对象 SQL> exec dbms_repcat.create_mview_repobject('test','ba_alccls','snapshot',gname =>'mv'); 建立refresh group DECLARE
SnapArray SYS.DBMS_UTILITY.UNCL_ARRAY;
BEGIN
SnapArray(1) := 'TEST.BA_ALCCLS';
SnapArray(2) := NULL;
SYS.DBMS_REFRESH.MAKE (
    name => 'TEST.mv_refresh'
    ,tab => SnapArray
    ,next_date => sysdate
    ,interval => 'sysdate+10/1440'
    ,implicit_destroy => FALSE
    ,lax => TRUE
    ,job => 0
    ,rollback_seg => NULL
    ,push_deferred_rpc => FALSE
    ,refresh_after_errors => FALSE
    ,purge_option => NULL
    ,parallelism => NULL
    ,heap_size => NULL
);
Commit;
END;
/