高级复制双向可更新视图

创建可更新的物化视图

 

1.创建复制管理用户repadmin（PROD和EMREP都要执行）

CONNECT sys/oracle@prod as sysdba

CREATE USER repadmin IDENTIFIED BYrepadmin;

GRANT COMMENT ANY TABLE TO repadmin;

GRANT LOCK ANY TABLE TO repadmin;

 

CONNECT sys/oracle@emrep as sysdba

CREATE USER repadmin IDENTIFIED BYrepadmin;

GRANT COMMENT ANY TABLE TO repadmin;

GRANT LOCK ANY TABLE TO repadmin;

 

2.配置repadmin用户

2.1 授予复制任何对象的权限（PROD和EMREP都要执行）

CONNECT sys/oracle@prod as sysdba

BEGIN

DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (

username => 'repadmin');

END;

/

CONNECT sys/oracle@emrep as sysdba

BEGIN

DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (

username => 'repadmin');

END;

/

 

2.2.注册传播用户（PROD和EMREP都要执行）

CONNECT sys/oracle@prod as sysdba

BEGIN

DBMS_DEFER_SYS.REGISTER_PROPAGATOR (

username => 'repadmin');

END;

/

CONNECT sys/oracle@emrep as sysdba

BEGIN

DBMS_DEFER_SYS.REGISTER_PROPAGATOR (

username => 'repadmin');

END;

/

 

3.在主站点操作（PROD）：

3.1 创建复制组

CONNECT repadmin/repadmin@prod

BEGIN

DBMS_REPCAT.CREATE_MASTER_REPGROUP (

gname => 'hr_repg');

END;

/

3.2 添加源表到复制组中

BEGIN

DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

gname => 'hr_repg',

TYPE => 'TABLE',

oname => 'employees',

sname => 'hr',

use_existing_object => TRUE,

copy_rows => FALSE);

END;

/

3.3 为源表设置复制支持

BEGIN

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (

sname => 'hr',

oname => 'employees',

TYPE => 'TABLE',

min_communication => TRUE);

END;

/

查看源表的状态

selectsname,master,status,owner from dba_repgroup;

3.4 激活复制组

BEGIN

DBMS_REPCAT.RESUME_MASTER_ACTIVITY (

gname => 'hr_repg');

END;

/

查看源表的状态

selectsname,master,status,owner from dba_repgroup;

然后再PROD库中建立物化视图日志：

HR@PROD > create materialized view logon employees with sequence,primary key including new values;

 

4.在物化视图站点操作（EMREP）：

 

4.1.创建repadmin用户的DBLink，用于传播和接收使用

create public database link prod connect tohr identified by hr using 'PROD';

 

再连接到repadmin用户下conn repadmin/repadmin

create database link prod connect torepadmin identified by repadmin using 'PROD';

 

我是在EMREP库中建立了一个HR用户，在HR用户下创建物化视图

CREATE MATERIALIZED VIEW EMP_UPD_VIEW   

 REFRESH FAST WITH PRIMARY KEY FOR UPDATE

  ASSELECT * FROM hr.employees@prod;

 

4.2 创建物化视图复制组（下面是到repadmin用户下完成的操作）

BEGIN

DBMS_REPCAT.CREATE_MVIEW_REPGROUP (

gname => 'hr_repg',

master => 'prod',

propagation_mode => 'ASYNCHRONOUS');

END;

/

4.3 在物化视图复制组中添加对象

BEGIN

  DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (

     gname => 'hr_repg',

     sname => 'hr',

     oname => 'EMP_UPD_VIEW',

     type => 'SNAPSHOT',

     min_communication => TRUE);

END;

/

这样就可以了