模拟利用MV进行数据迁移

利用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.

--最后创建与表有依赖关系的对象