使用物化视图的 ON PREBUILT TABLE这样的一种方式进行同步迁移数据。
SQL>desc T1
NameNull?Type
------------------------------------------- -------- --------------------------------------------------------
ANOT NULL VARCHAR2(10)
BVARCHAR2(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;
AB
---------- -----
12
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;
AB
---------- -----
12
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@]