利用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.
--最后创建与表有依赖关系的对象