類比利用MV進行資料移轉

http://boylook.itpub.net/post/43144/520543

利用MV的資料移轉最大的特點是比較靈活，可以實現跨平台，跨資料庫版本遷移，而且能夠實現資料的重組最佳化。該方式的實現原理要求在源表對象有一個主鍵，用於MV重新整理。在源表上建立MV日誌，再在目標資料庫建立結構一樣的表，然後在目標資料庫上採用prebuilt方式建立MV，第一次採用完全重新整理，然後一直採用增量重新整理，等到要切換的時候，只要重新整理增量的日誌，刪除MV，保留目標表即可。

--建立源表

SQL> create table from_table(id number,num number);

Table created.

--添加主鍵

SQL> alter table from_table add constraint pk_from primary key(id);

Table altered.

--建立目標表
。然後在該表上建立主鍵或者非空的唯一約束。

SQL> create table to_table(id number,num number);

Table created.

SQL> alter table to_table add constraint pk_to primary key(id);

Table altered.

SQL> insert into from_table select rownum,rownum*100 from dba_objects where rownum <=10;

10 rows created.

SQL> commit;

Commit complete.

--在源表建立MV日誌

SQL> create materialized view log on from_table;

Materialized view log created.

--在目標表上採用prebuilt方式建立MV

SQL> create materialized view to_table on prebuilt table refresh fast as select * from from_table;

Materialized view created.

SQL> select count(*) from to_table;

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

--執行完全重新整理
SQL> exec dbms_mview.refresh('TO_TABLE',method =>'Complete');

PL/SQL procedure successfully completed.

SQL> select count(*) from to_table;

COUNT(*)
----------
10

--執行一次增量重新整理。增量重新整理之前一定要保證源表和目標表上都存在主鍵，否則無法完成增量重新整理

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

PL/SQL procedure successfully completed.

SQL> select * from to_table;

ID NUM
---------- ----------
1 100
2 200
3 300
4 400
5 500
6 600
7 700
8 800
9 900
10 1000

10 rows selected.

--建立自動重新整理的作業，每30秒同步一次增量日誌。

SQL> @create_program
附：cat create_program.sql
begin
dbms_scheduler.create_program
(
program_name =>'refresh_to_table',
program_type =>'PLSQL_BLOCK',
program_action =>'begin dbms_mview.refresh(''TO_TABLE'');end;',
enabled =>TRUE
);
end;
/

PL/SQL procedure successfully completed.

SQL> @create_scheduler
附：cat create_scheduler.sql
begin
dbms_scheduler.create_schedule
(
schedule_name =>'every_30_seconds',
start_date =>systimestamp,
repeat_interval =>'FREQ=SECONDLY;INTERVAL=30'
);
end;
/

PL/SQL procedure successfully completed.

SQL> @create_job
附：cat create_job.sql
begin
dbms_scheduler.create_job
(
job_name =>'secondly_refresh',
program_name =>'refresh_to_table',
schedule_name =>'every_30_seconds',
enabled =>TRUE
);
end;
/

PL/SQL procedure successfully completed.

--運行作業

SQL> exec dbms_scheduler.run_job('secondly_refresh');

PL/SQL procedure successfully completed.

--對源表繼續操作

SQL> insert into from_table values (11,1);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from to_table;

COUNT(*)
----------
11

SQL> insert into from_table select rownum+11,rownum*1000 from dba_objects where rownum <=9;

9 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from to_table;

COUNT(*)
----------
20

SQL> select * from to_table;

ID NUM
---------- ----------
1 100
2 200
3 300
4 400
5 500
6 600
7 700
8 800
9 900
10 1000
11 1

ID NUM
---------- ----------
13 2000
14 3000
20 9000
17 6000
18 7000
12 1000
15 4000
16 5000
19 8000

20 rows selected.

SQL> update from_table set num = 1500 where id = 11;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from to_table;

ID NUM
---------- ----------
1 100
2 200
3 300
4 400
5 500
6 600
7 700
8 800
9 900
10 1000
11 1500

ID NUM
---------- ----------
13 2000
14 3000
20 9000
17 6000
18 7000
12 1000
15 4000
16 5000
19 8000

20 rows selected.

SQL> exec dbms_scheduler.drop_job('secondly_refresh');

PL/SQL procedure successfully completed.

SQL> delete from to_table where rownum = 1;
delete from to_table where rownum = 1
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

--等重新整理完成，刪除MV log與MV。在目標資料庫上，刪除MV後，表和資料仍然存在

SQL> drop materialized view to_table;

Materialized view dropped.

SQL> drop materialized view log on from_table;

Materialized view log dropped.

SQL> select * from to_table;

ID NUM
---------- ----------
1 100
2 200
3 300
4 400
5 500
6 600
7 700
8 800
9 900
10 1000
11 1500

ID NUM
---------- ----------
13 2000
14 3000
20 9000
17 6000
18 7000
12 1000
15 4000
16 5000
19 8000

20 rows selected.

--最後建立與表有依賴關係的對象

本文出自 “MIKE老畢的部落格” 部落格，請務必保留此出處http://boylook.blog.51cto.com/7934327/1298617