主站点
创建用户：
SQL> create user test identified by test
  2  ;

User created.

SQL> grant connect,resource,unlimited tablespace to test;

Grant succeeded.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test
Connected.
创建需要迁移的表：
SQL> create table big_t1 as select * from all_objects;

Table created.

SQL> select count(1) from big_t1;

  COUNT(1)
----------
     49331

创建VIEW LOG，这里出现问题，应该该表没有主键，需要创建主键。

SQL> create materialized view log on big_t1;
create materialized view log on big_t1
*
ERROR at line 1:
ORA-12014: table 'BIG_T1' does not contain a primary key constraint

SQL> desc big_t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

创建主键：

SQL> alter table big_t1 add primary key (object_id);

Table altered.

SQL> create materialized view log on big_t1;

Materialized view log created.

SQL> show user
USER is "TEST"

复制端：

1、创建DBLINK

create public database link asm using 'MDCL';

2、生成需要复制的表，创建主键，并在该表上建立PREBUILT MV

SQL> create table big_t1 as select * from big_t1@asm where 1=2;

表已创建。

SQL> alter table big_t1 add primary key (object_id);

表已更改。

SQL> select count(*) from big_t1;

  COUNT(*)                                                                     
----------                                                                     
         0                         

SQL> create materialized view big_t1 on prebuilt table refresh fast as select * from big_t1@asm;

实体化视图已创建。

3、做完全刷新，并验证数据

SQL> exec dbms_mview.refresh('BIG_T1','C');

PL/SQL 过程已成功完成。

SQL> select count(*) from big_t1;

  COUNT(*)                                                                     
----------                                                                     
     49332                 

在主站点插入数据

SQL> insert into big_t1(owner,object_name,object_id,created,last_ddl_time) values('test','test',100000,'22-JUL-05','22-JUL-05');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from big_t1;

  COUNT(*)
----------
     49333

在复制站点进行增量刷新

SQL> exec dbms_mview.refresh('BIG_T1');

PL/SQL 过程已成功完成。

SQL> select count(*) from big_t1;

  COUNT(*)                                                                     
----------                                                                     
     49333  

在主站点再插入数据

SQL> insert into big_t1(owner,object_name,object_id,created,last_ddl_time) values('test','test',100001,'22-JUL-05','22-JUL-05');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select count(*) from big_t1;

  COUNT(*)                                                                     
----------                                                                     
     49334

在复制站点，增量刷新，然后删除prebuilt mv,检查表中数据

QL> exec dbms_mview.refresh('BIG_T1');

PL/SQL 过程已成功完成。

SQL> select count(*) from big_t1;

  COUNT(*)                                                                     
----------                                                                     
     49334                                                                     

SQL> drop materialized view big_t1;

实体化视图已删除。

SQL> select count(*) from big_t1;

  COUNT(*)                                                                     
----------                                                                     
     49334 

                                                                    
实验完成