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後，物化檢視需要全量重新整理或者重建。