Oracle11新特性——支持对物化视图日志的在线重定义

Oracle的在线重定义功能就是利用了物化视图的功能，通过物化视图日志的记录功能，来同步目标表和基表的数据。因此，在11g以前，建立了物化视图日志的表是无法进行在线重定义操作的。
在11g中，Oracle会利用现有的物化视图日志来完成在线重定义的功能。同时，物化视图日志也可以作为表的从属信息同步到目标上。不过在同步完成后，需要物化视图执行完全刷新。

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as yuzh

SQL> CREATE MATERIALIZED VIEW LOG ON YU_TEST1;
 
Materialized view log created
 
SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'YU_TEST1');
 
PL/SQL procedure successfully completed

上面执行时没有报错。

 

SQL> CREATE MATERIALIZED VIEW MV_TEST REFRESH FAST AS SELECT * FROM YU_TEST1;
 
Materialized view created

SQL> select count(*) from YU_TEST1;
 
  COUNT(*)
----------
     31956
 
SQL> select count(*) from MV_TEST;
 
  COUNT(*)
----------
     31956

SQL> delete from yu_test1 where client_id='AANJE7CD5I4N4VLSOBD6UHGG3SJ7MNIB';
 
1 row deleted
 
SQL> commit;
 
Commit complete
 
SQL> select count(*) from YU_TEST1;
 
  COUNT(*)
----------
     31955
 
SQL> select count(*) from MV_TEST;
 
  COUNT(*)
----------
     31956
    
SQL> EXEC DBMS_MVIEW.REFRESH('MV_TEST');
 
PL/SQL procedure successfully completed
 
SQL> select count(*) from YU_TEST1;
 
  COUNT(*)
----------
     31955
 
SQL> select count(*) from MV_TEST;
 
  COUNT(*)
----------
     31955