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