建立可更新的物化视图
发布时间：2010/6/25 15:32:47 来源：城市学习网 编辑：ziteng
环境
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; [NextPage] 建立复制组
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;
/
