ORACLE 物化视图维护中相关基表发生字段长度类DDL变更后需要重建或者全量刷新

    最近，ORACLE物化视图维护中发现一个问题，物化视图相关基表发生字段长度类DDL变更后，如果物化视图执行FORCE

或者FAST刷新后，物化视图相关user_mview_keys和dba_mview_keys记录的相关基表信息丢失，物化视图相关的状态信息user_mviews中的​STALENESS为UNUSABLE且dba_objects中的status为invalid，但是增量刷新并不影响物化视图数据的同

步；如果发生DDL变更后，重新编译物化视图并且对物化视图全量刷新或者重建物化视图，则物化视图状态正常。

    以下是物化视图维护中相关基表发生字段长度类DDL变更后需要重建或者全量刷新的场景重现。

    步骤1、创建物化视图相关用户test并授权

create user test identified by test default tablespace users;

grant CONNECT to test;

grant RESOURCE to test;

GRANT   CREATE MATERIALIZED VIEW  TO test; 

    步骤2、确认test用户的创建和权限

select * from user_sys_privs;

    USERNAME PRIVILEGE ADMIN_OPTION

1 TEST CREATE MATERIALIZED VIEW NO

2 TEST UNLIMITED TABLESPACE NO

select * from USER_ROLE_PRIVS;   

USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED

1 TEST CONNECT NO YES NO

2 TEST RESOURCE NO YES NO

    步骤3、创建物化视图基表

create table TEST_MV(id number,mdate date,name varchar(20) primary key);

    步骤4、创建物化视图日志

CREATE MATERIALIZED VIEW LOG ON TEST_MV

   WITH  primary key

   INCLUDING NEW VALUES;

    步骤5、创建测试物化视图

CREATE MATERIALIZED VIEW MV_TEST_MV 

build immediate REFRESH force on demand with primary key 

AS select * from TEST_MV;

    步骤6、物化视图同步的列（基表字段长度发生变化后，物化视图直接增量刷新会导致相关物化视图数据丢失）

select * from user_mview_keys  a where a.mview_name='MV_TEST_MV';  

1 TEST MV_TEST_MV 1 ID TEST TEST_MV TEST_MV TABLE ID

2 TEST MV_TEST_MV 2 MDATE TEST TEST_MV TEST_MV TABLE MDATE

3 TEST MV_TEST_MV 3 NAME TEST TEST_MV TEST_MV TABLE NAME

 

select * from dba_mview_keys  a where a.mview_name='MV_TEST_MV';  

1 TEST MV_TEST_MV 1 ID TEST TEST_MV TEST_MV TABLE ID

2 TEST MV_TEST_MV 2 MDATE TEST TEST_MV TEST_MV TABLE MDATE

3 TEST MV_TEST_MV 3 NAME TEST TEST_MV TEST_MV TABLE NAME

    步骤7、源表插入测试数据

insert into TEST_MV(id,mdate,name) values(1,sysdate,'11');

    步骤8、刷新物化视图

begin

 dbms_mview.refresh('MV_TEST_MV','force');

end;

/

    步骤9、查看基表、物化视图数据及物化视图状态信息

SQL> select * from MV_TEST_MV;

 

ID MDATE     NAME

---------- --------- ------------------------------

 1 23-JUL-20 11

SQL> select * from TEST_MV;

ID MDATE     NAME

---------- --------- ------------------------------

 1 23-JUL-20 11

SQL> select * from mlog$_test_mv;

NAME      SNAPTIME$ D O CHANGE_VECTOR$$    XID$$

-------------------- --------- - - -------------------------------------------------- ----------

86

SQL> select owner,mview_name,STALENESS from user_mviews;

 

OWNER    MVIEW_NAME STALENESS

---------- ---------- -------------------

TEST    MV_TEST_MV FRESH

SQL> conn / as sysdba

Connected.

SQL> select owner,object_name,object_type,status from dba_objects where object_name='MV_TEST_MV';

OWNER    OBJECT_NAME     OBJECT_TYPE  STATUS

---------- ---------------------------------------------------------------------- ------------------- -------

TEST    MV_TEST_MV     MATERIALIZED VIEW VALID

TEST    MV_TEST_MV     TABLE VALID

    步骤9、修改主键列长度

alter table TEST_MV modify(name varchar(30));

alter table MV_TEST_MV modify(name varchar(30));

insert into TEST_MV(id,mdate,name) values(2,sysdate,'22');

commit;

    步骤10、刷新物化视图

begin

 dbms_mview.refresh('MV_TEST_MV','FORCE');

end;

/

    步骤10之后，物化视图MV_TEST_MV状态异常并且数据库dba_mview_keys和user_mview_keys视图无相关物化视图的

基本信息

--查看物化视图基表信息无存在

SQL>  select * from dba_mview_keys  a where a.mview_name='MV_TEST_MV';

no rows selected

SQL>

SQL>  select * from user_mview_keys  a where a.mview_name='MV_TEST_MV';

no rows selected

SQL>

--查看物化视图状态信息异常

SQL> select owner,mview_name,STALENESS from user_mviews;

OWNER    MVIEW_NAME STALENESS

---------- ---------- -------------------

TEST    MV_TEST_MV UNUSABLE

 

SQL> conn / as sysdba

Connected.

SQL> select owner,object_name,object_type,status from dba_objects where object_name='MV_TEST_MV';

OWNER    OBJECT_NAME     OBJECT_TYPE  STATUS

---------- --------------------------------------------------------------------- ------------------- -------

TEST    MV_TEST_MV     MATERIALIZED VIEW INVALID

TEST    MV_TEST_MV     TABLE VALID

    如果在步骤9之后，不执行对物化视图进行增量刷新，观察物化视图状态信息如下：

SQL> select owner,mview_name,STALENESS from user_mviews;

OWNER    MVIEW_NAME STALENESS

---------- ---------- -------------------

TEST    MV_TEST_MV NEEDS_COMPILE

SQL> conn / as sysdba

Connected.

SQL> select owner,object_name,object_type,status from dba_objects where object_name='MV_TEST_MV';

OWNER    OBJECT_NAME     OBJECT_TYPE  STATUS

---------- ---------------------------------------------------------------- ------------------- -------

TEST    MV_TEST_MV     MATERIALIZED VIEW INVALID

TEST    MV_TEST_MV     TABLE VALID

    步骤9执行后，按照物化视图状态信息提示重新编译物化视图并执行增量刷新，问题依然存在

SQL> show user;

USER is "TEST"

SQL> ALTER MATERIALIZED VIEW TEST.MV_TEST_MV COMPILE;

Materialized view altered.

SQL> 

begin

 dbms_mview.refresh('MV_TEST_MV','FORCE');

end;

/

SQL>   2    3    4  

PL/SQL procedure successfully completed.

 

--查看物化视图状态

SQL> select owner,mview_name,STALENESS from dba_mviews;

no rows selected

 

SQL> select owner,object_name,object_type,status from dba_objects where object_name='MV_TEST_MV';

OWNER    OBJECT_NAME     OBJECT_TYPE  STATUS

---------- ------------------------------------------------------------------- ------------------- -------

TEST    MV_TEST_MV     MATERIALIZED VIEW INVALID

TEST    MV_TEST_MV     TABLE VALID

    如果步骤9之后，对物化视图重新编译后执行全量刷新，则物化视图一切正常。

SQL> show user;

USER is "TEST"

SQL> ALTER MATERIALIZED VIEW TEST.MV_TEST_MV COMPILE;

Materialized view altered.

SQL> 

 

SQL> 

begin

 dbms_mview.refresh('MV_TEST_MV','COMPLETE');

end;

/SQL>   2    3    4  

SQL> 

PL/SQL procedure successfully completed.

--数据库基表记录物化视图信息存在

 

SQL> select * from user_mview_keys  a where a.mview_name='MV_TEST_MV';

OWNER        MVIEW_NAME       POSITION_IN_SELECT CONTAINER_COLUMN DETAILOBJ_OWNER         DETAILOBJ_NAME       DETAILOBJ_ALIAS      DETAI DETAILOBJ_COLUMN

------------------------------ ------------------------------ ------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----- ------------------------------

TEST        MV_TEST_MV        1 ID TEST        TEST_MV       TEST_MV      TABLE ID

TEST        MV_TEST_MV        2 MDATE TEST        TEST_MV       TEST_MV      TABLE MDATE

TEST        MV_TEST_MV        3 NAME TEST        TEST_MV       TEST_MV      TABLE NAME

SQL>conn / as sysdba

connected.

SQL> l

  1* select * from dba_mview_keys  a where a.mview_name='MV_TEST_MV'

SQL> /

OWNER        MVIEW_NAME       POSITION_IN_SELECT CONTAINER_COLUMN DETAILOBJ_OWNER         DETAILOBJ_NAME       DETAILOBJ_ALIAS      DETAI DETAILOBJ_COLUMN

------------------------------ ------------------------------ ------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----- ------------------------------

TEST        MV_TEST_MV        1 ID TEST        TEST_MV       TEST_MV      TABLE ID

TEST        MV_TEST_MV        2 MDATE TEST        TEST_MV       TEST_MV      TABLE MDATE

TEST        MV_TEST_MV        3 NAME TEST        TEST_MV       TEST_MV      TABLE NAME

SQL> 

--物化视图状态信息正常

SQL> select owner,mview_name,STALENESS from user_mviews;

no rows selected

SQL> conn test/test

Connected.

SQL> /

OWNER    MVIEW_NAME STALENESS

---------- ---------- -------------------

TEST    MV_TEST_MV FRESH

 

SQL> conn / as sysdba

Connected.

SQL> select owner,object_name,object_type,status from dba_objects where object_name='MV_TEST_MV';

OWNER    OBJECT_NAME     OBJECT_TYPE  STATUS

---------- ------------------------------ ----------------------------------------- ------------------- -------

TEST    MV_TEST_MV     MATERIALIZED VIEW VALID

TEST    MV_TEST_MV     TABLE VALID

    如果物化视图基表发生DDL后，重建物化视图也能解决物化视图状态异常问题，这里不再演示。

 

结论：对ORACLE数据库物化视图维护工作中，我们需要谨慎对物化视图相关基表执行DDL操作，DDL操作会导致物化视图

状态异常，关于该问题，ORACLE官方给出的说法是物化视图基表发生DDL后，物化视图相关状态异常和数据库基表记录信

息不存在是ORACLE的正常行为，结合本文实验，物化视图基表发生DDL后，物化视图需要全量刷新或者重建。