ITPub博客

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

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

原创 Oracle 作者:清风艾艾 时间:2020-10-31 18:07:53 0 删除 编辑

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




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29357786/viewspace-2731393/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
个人喜欢IT行业,目前从事数据库工作,包括Oracle、mysql、mongodb、sqlserver等数据库的维护,喜欢专研开发技术,尤其对java程序的开发感兴趣。工作经历上,在中国联通系统集成公司、中公网医疗信息技术有限公司做过数据库技术支持;目前在海量数据,负责华东区oracle、mysql、mongodb的维护工作。

注册时间:2015-01-30

  • 博文量
    223
  • 访问量
    398704