预置表物化视图的增量刷新前提条件

HP的一位朋友最近要进行一次数据迁移,为了尽量减少停机时间,朋友采用物化视图的方式进行迁移。

大体的迁移逻辑如下：

在目的库上根据源库的表结构建立相同的表，然后再表上建立预置表的物化视图。
在源库中建立物化视图日志。在业务进行的时候，进行一次完全刷新，然后停机后，将增量数据刷新过去。

然后删掉物化视图。

朋友问到这种情况下是否能建立基于ROWID的增量刷新的物化视图呢？

一个简单的测试，答案就可以知晓：

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> CREATE TABLE TEST(ID INT PRIMARY KEY,NAME VARCHAR2(20));

Table created.

SQL> CREATE MATERIALIZED VIEW LOG ON TEST WITH ROWID;

Materialized view log created.

源库是一个LINUX上的11GL环境，在上面建立测试表TEST，并且建立基于ROWID的物化视图日志。

目标库是一个WINDOWS下的10G环境，下面在目标库上建立DATABASE LINK及其表结构。

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> CREATE DATABASE LINK LINK1 CONNECT TO dbtest IDENTIFIED BY "dbtest"
  2  using 'dbtest';

数据库链接已创建。

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
------------------------------------------------------------------------------

YANSP.REGRESS.RDBMS.DEV.US.ORACLE.COM

SQL> SELECT * FROM ;

GLOBAL_NAME
------------------------------------------------------------------------------

DBSERVER

SQL> CREATE TABLE TEST(ID INT PRIMARY KEY,NAME VARCHAR2(20));

表已创建。

SQL> CREATE MATERIALIZED VIEW TEST
  2  ON PREBUILT TABLE WITH REDUCED PRECISION
  3  AS
  4  SELECT * FROM ;

实体化视图已创建。

SQL> SELECT * FROM TEST;

未选定行

预置表的物化视图已经建立。

在源库中插入一条记录：

源库操作：

SQL> INSERT INTO TEST VALUES(1,'YANSHOUPENG');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TEST;

        ID NAME
---------- --------------------
         1 YANSHOUPENG

SQL> SELECT COUNT(1) FROM MLOG$_TEST;

  COUNT(1)
----------
         1

目标库操作：

SQL> EXEC DBMS_MVIEW.REFRESH('TEST','F');
BEGIN DBMS_MVIEW.REFRESH('TEST','F'); END;

*
第 1 行出现错误:
ORA-12031: 不能使用 "YIKAIDEV"."TEST" 上实体化视图日志中的主键列
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: 在 line 1

SQL> EXEC DBMS_MVIEW.REFRESH('TEST','C');

PL/SQL 过程已成功完成。

SQL> SELECT * FROM TEST;

        ID NAME
---------- --------------------
         1 YANSHOUPENG

物化视图进行增量刷新报ORA-12031错误，而完全刷新是没有任何问题的。
那么物化视图为何不支持增量刷新呢？
可以通过如下的方法进行定位物化视图不能支持增量刷新的原因：

 

SQL> @?/rdbms/admin/utlxmv.sql

表已创建。

SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('TEST');

PL/SQL 过程已成功完成。

CAPABILITY_NAME                P RELATED_TEXT         MSGTXT
------------------------------ - -------------------- --------------------------
PCT                            N
REFRESH_COMPLETE               Y
REFRESH_FAST                   N
REWRITE                        N
PCT_TABLE                      N TEST                 关系不是一个已分区的表
REFRESH_FAST_AFTER_INSERT      N YIKAIDEV.TEST        实体化视图日志必须具有主键
REFRESH_FAST_AFTER_ONETAB_DML  N                      查看禁用 REFRESH_FAST_AFTER_INSERT 的原 因
REFRESH_FAST_AFTER_ANY_DML     N                      查看禁用 REFRESH_FAST_AFTER_ONETAB_DML的原因
REFRESH_FAST_PCT               N                      PCT 不可能在实体化视图中的任何从表上
REWRITE_FULL_TEXT_MATCH        N TEST                 实体化视图引用了 FROM 列表中的远程表或视图
REWRITE_FULL_TEXT_MATCH        N                      在实体化视图上禁用查询重写
REWRITE_PARTIAL_TEXT_MATCH     N                      实体化视图无法支持任何类型的查询重写
REWRITE_PARTIAL_TEXT_MATCH     N                      在实体化视图上禁用查询重写
REWRITE_GENERAL                N                      实体化视图无法支持任何类型的查询重写
REWRITE_GENERAL                N                      在实体化视图上禁用查询重写
REWRITE_PCT                    N                      无法进行一般重写, 或者 PCT 不可能在任何从表上
PCT_TABLE_REWRITE              N TEST                 关系不是一个已分区的表

已选择17行。

SQL>

可以看到物化视图不支持增量刷新的原因是实体化视图必须具有主键。

 

 

下面在源库重新建立基于主键的物化视图日志：

源库操作：

SQL> DROP MATERIALIZED VIEW LOG ON TEST;

Materialized view log dropped.

SQL> CREATE MATERIALIZED VIEW LOG ON TEST WITH PRIMARY KEY;

Materialized view log created.

SQL> SELECT * FROM TEST;

        ID NAME
---------- --------------------
         1 YANSHOUPENG

SQL> INSERT INTO TEST VALUES(2,'ZHANGYEHAI');

1 row created.

SQL> COMMIT;

Commit complete.

 

目标库操作：

 

SQL> SELECT * FROM TEST;

        ID NAME
---------- --------------------
         1 YANSHOUPENG

SQL> SELECT * FROM ;

        ID NAME
---------- ----------------------------------------
         1 YANSHOUPENG
         2 ZHANGYEHAI

SQL> EXEC DBMS_MVIEW.REFRESH('TEST','F');
BEGIN DBMS_MVIEW.REFRESH('TEST','F'); END;

*
第 1 行出现错误:
ORA-12034: "YIKAIDEV"."TEST" 上的实体化视图日志比上次刷新后的内容新
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: 在 line 1

SQL> EXEC DBMS_MVIEW.REFRESH('TEST','C');

PL/SQL 过程已成功完成。

 

SQL> INSERT INTO VALUES(3,'CPIC');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM ;

        ID NAME
---------- ----------------------------------------
         3 CPIC
         1 YANSHOUPENG
         2 ZHANGYEHAI

SQL> SELECT * FROM TEST;

        ID NAME
---------- --------------------
         1 YANSHOUPENG
         2 ZHANGYEHAI

SQL> EXEC DBMS_MVIEW.REFRESH('TEST','F');

PL/SQL 过程已成功完成。

SQL> SELECT * FROM ;

        ID NAME
---------- ----------------------------------------
         3 CPIC
         1 YANSHOUPENG
         2 ZHANGYEHAI

 

从上面可以看到，基于预置表的物化视图的增量刷新需要物化视图日志带有主键，因此基表也必须有主键，因此不能建立基于ROWID的物化视图。