ITPub博客

首页 > 数据库 > Oracle > materialized view实现分布式复制环境

materialized view实现分布式复制环境

原创 Oracle 作者:tianshiguodong 时间:2019-01-14 18:08:55 0 删除 编辑

################################分布式环境下通过materialized view实现表高级复制

1.在主站点建立snapproxy用户

create user snapproxy identified by "snap001snap" default tablespace users;

BEGIN

    dbms_repcat_admin.register_user_repgroup(

            username =>       'snapproxy',

            privilege_type => 'proxy_snapadmin',

            list_of_gnames =>  NULL);

END;

/

grant create session,select any table to snapproxy;


2.在主站点以要复制表的属主创建表的物化视图日志:(这里为hr用户)

conn hr/hr;

create snapshot log on employees with rowid,primary key;

create snapshot log on departments with rowid,primary key;


3.在物化视图站点创建复制表的目标用户:

create user mview identified by mview default tablespace usres;

grant connect,resource,create snashot,create database link to mview;


4.在物化视图站点创建物化视图刷新用户并赋权:

create user snapadmin identified by "snap001snap" default tablespace users;

EXECUTE dbms_repcat_admin.grant_admin_any_schema('snapadmin');

grant comment any table,lock any table,create any materialized view,alert any materialized view to snapadmin;


5.在物化视图站点以物化视图用户mview创建到snapproxy用户的dblink及物化视图:

create database link presale connect to snapproxy identified by "snap001snap" using '130.84.208.52:1621/presale';

create materialized view employees_mv refresh fast as select * from hr.employees@presale;

create materialized view departments_mv refresh fast as select * from hr.departments@presale;


6.snapadmin用户创建刷新组

begin

        dbms_refresh.make(

                name => 'RG_MVIEW_OWNER',

                list => 'mview.employees_mv,mview.departments_mv',

                next_date => sysdate,

                interval => 'SYSDATE + 1/1440',

                implicit_destroy => true,

                lax => true);

end;

/


7.初始化刷新

execute dbms_refresh.refresh('RG_MVIEW_OWNER');


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16807927/viewspace-2557304/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-07-30

  • 博文量
    18
  • 访问量
    47187