使用物化视图的 ON PREBUILT TABLE 这样的一种方式进行同步迁移数据。

SQL>desc T1

Name Null? Type

------------------------------------------- -------- --------------------------------------------------------

A NOT NULL VARCHAR2(10)

B VARCHAR2(5)

SQL>select index_name from user_indexes where table_name='T1';

INDEX_NAME

------------------------------

T1_A_IND

1 row selected.

SQL>

SQL>create table T2 as select * from T1 where 1=2;

Table created.

SQL>create materialized view log on T1;

Materialized view log created.

SQL>create materialized view T2 on prebuilt table refresh fast as select * from T1;

Materialized view created.

SQL>exec dbms_mview.refresh('T2','Complete');

PL/SQL procedure successfully completed.

SQL>select * from T2;

no rows selected

SQL>insert into T1 values('1','2');

1 row created.

SQL>commit;

Commit complete.

SQL>select * from T2;

no rows selected

SQL>exec dbms_mview.refresh('T2','Complete');

PL/SQL procedure successfully completed.

SQL>select * from T2;

A B

---------- -----

1 2

1 row selected.

SQL>delete from T2;

delete from T2

*

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

SQL>drop materialized view T2;

Materialized view dropped.

SQL>select * from T2;

A B

---------- -----

1 2

1 row selected.

SQL>delete from T2;

1 row deleted.

SQL>rollback;

Rollback complete.

SQL>

在原表上建立log，再建一张目标表，然后再建一个和目标表名称一样的物化视图，然后执行同步即可。此时对t2表（其实是对物化视图操作的）进行的查询操作是可以的，但是删除更新插入不能够执行，会报ERROR at line 1:ORA-01732: data manipulation operation not legal on this view。当刷新完毕后可以删除物化视图，此时表t2是不受影响的，这样就使用oracle的内部方式完成了数据迁移。[@more@]