使用prebuilt table 方式遷移資料

使用物化檢視的 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@]