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 11SQL> 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后，物化视图需要全量刷新或者重建。