利用 mviews on prebuilt table 进行增量刷新数据

 
1.在源库上创建表和mview log

create table andy_01 as select * from dba_objects ;

select count(*) from andy_01;

--为基表建立主键，防止ORA-12014 does not contain a primary key constraint
delete from andy_01 where object_id is null;
alter table andy_01 add constraint pk_andy_01 primary key(object_id);
create materialized view log on andy_01;

--源库查询 生成 materialized view log 对象名

 select * from user_objects where object_name like '%MLOG$%';
OBJECT_NAME
-----------------------------------------------------------------------------
I_MLOG$_ANDY_01   --index
MLOG$_ANDY_01    -> materialized view log 对象名

select *from MLOG$_ANDY_01

2. 授权

-- 授权目标库用户有查询 源库 materialized view log 的权限
 grant select on scott.MLOG$_ANDY_01 to andy;
 
 grant select on scott.andy_01 to andy;

3.在目标数据库上创建与该表一样的表，并在该表上创建prebuilt mv

  create table andy_02 as select * from  andy_01 where 1=2;       --异库加上@dblink_name

  select count(*) from andy_02;
  COUNT(*)
----------
         0
-- sys用户为目标用户授权
SQL> GRANT CREATE MATERIALIZED VIEW TO andy;

Grant succeeded.
-- 目标库创建 materialized view 

SQL> create materialized view andy_02 on prebuilt table refresh fast as select * from  andy_01;
Materialized view created.

select *From dba_objects b where b.OBJECT_NAME like '%ANDY_02%'

select *From andy_02
 
4.做完全刷新和增量刷新

begin  dbms_mview.refresh('andy_02','Complete');
end;
 
 
 
--此时模拟在做完全刷新过程中，源库的表又发生了变化
SQL> insert into andy_01(object_id,owner) values(666666,'test');

1 row created.
SQL>  commit;

Commit complete.
--再做增量刷新
 select count(*) from andy_02;

  COUNT(*)
----------
     88765     
begin  dbms_mview.refresh('andy_02','Complete');
end;

PL/SQL procedure successfully completed.
 
SQL> select count(1) from andy_01;

 

5.停机切换,做最后一次刷新，然后删除源库的mview log和目标库的mview

SQL> exec dbms_mview.refresh('andy_01');
PL/SQL procedure successfully completed.

6. 迁移完毕，清除 materialized view 与 materialized view log

-- 清除 目标库 materialized view
SQL> drop materialized view andy_02;
Materialized view dropped. 
SQL> select count(*) from andy_02;
  COUNT(*)
----------
     88766
这里删除的mview(andy_01)是prebuilt mv，所以删除该mview,并不删除相应的表。---
-- 清除源库 materialized view log
SQL>  drop materialized view log on  ANDY_01;

Materialized view log dropped.
 
SQL> select * from user_objects where object_name like '%ANDY%';

no rows selected