成功测试两库中数据表人工同步记录

--在源库

create table srcreader as select cert_id,name,dept from reader where 1=2;--创建表结构

alter table srcreader add constraint pk_certid primary key(cert_id) using index;--定义主键并创建索引

insert into srcreader select cert_id,name,dept from reader where length(cert_id)=4 and cert_id like '11%';

 commit; --插入部分数据

 create materialized view log on srcreader；--在源表上建立物化视图日志

 --在需库

 conn scott/tiger@orc9

 SQL> create database link dblink_tlibsys connect to tlibsys identified by tlibsys using 'tlibsys';

数据库链接已创建。--注意如果低于11版本的库 需要在用户名和密码加双引号，否则会因为自动转为大写导致用户名或密码无效错误

SQL> create table tgreader as select * from srcreader@dblink_tlibsys where 1=2;

表已创建。

SQL> create materialized view tgreader on prebuilt table refresh fast with primary key on commit as select * from srcreader@dblink_tlibsys;

create materialized view tgreader on prebuilt table refresh fast with primary key on commit as select * from srcreader@dblink_tlibsys

        *

第 1 行出现错误:

ORA-01031: 权限不足

SQL> conn sys/sys1 as sysdba

ERROR:

ORA-12560: TNS: 协议适配器错误

警告: 您不再连接到 ORACLE。

SQL> conn sys/sys1@orcl9 as sysdba

已连接。

SQL> grant create materialized view to scott;

授权成功。

SQL> conn scott/tiger

ERROR:

ORA-12560: TNS: 协议适配器错误

警告: 您不再连接到 ORACLE。

SQL> conn scott/tiger@orcl9

已连接。

SQL> create materialized view tgreader on prebuilt table refresh fast with primary key on commit as select * from srcreader@dblink_tlibsys;

create materialized view tgreader on prebuilt table refresh fast with primary key on commit as select * from srcreader@dblink_tlibsys

        *

第 1 行出现错误:

ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性

SQL> create materialized view tgreader on prebuilt table refresh fast with primary key on demand as select * from srcreader@dblink_tlibsys;

实体化视图已创建。

SQL> select * from tgreader;

未选定行

SQL> exec dbms_mview.refresh('tgreader','f');--在开始不能用增量同步？？？？

PL/SQL 过程已成功完成。

SQL> select * from tgreader;

未选定行

SQL> exec dbms_mview.refresh('tgreader','c');--执行完全同步

PL/SQL 过程已成功完成。

SQL> select count(*) from tgreader;  --查看数量，以测试在增量同步后数据有无增加

  COUNT(*)

----------

        94

--在源表添加数据

insert into srcreader select cert_id,name,dept from reader where length(cert_id)=4 and cert_id like '12%';

--在需库执行增量看有无数据添加

SQL> exec dbms_mview.refresh('tgreader','f');

PL/SQL 过程已成功完成。

SQL> select count(*) from tgreader;

  COUNT(*)

----------

       189