Oracle物化视图失效的几种情况及测试

港控/mmm° 2022-05-13 01:36 708阅读 0赞

说明：物化视图（Materialized Views）是查询的结果集，所有原表和视图的变更都将导致物化视图的失效，最近就发现了一个物化视图经常失效的问题，以下是整理的文档。

一、物化视图状态查询：Oracle提供了一个视图用于查询物化视图的状态USER\_MVIEWS,其中列STALENESS，用于显示当前物化视图的状态

Relationship between the contents of the materialized view and the contents of the materialized view’s masters:  
•FRESH - Materialized view is a read-consistent view of the current state of its masters（最新状态：当前物化视图的内容出于最新的状态）  
•STALE - Materialized view is out of date because one or more of its masters has changed. If the materialized view was FRESH before it became STALE, then it is a read-consistent view of a former state of its masters.（陈旧状态：物化视图引用的主表已经更新，但是物化视图没有刷新，所以内容相对主表来说是旧的）

•NEEDS\_COMPILE - Some object upon which the materialized view depends has changed. An ALTER MATERIALIZED VIEW…COMPILE statement is required to validate this materialized view and compute the staleness of the contents.（需要编译：物化视图引用的主表比如视图，进行了重建后相应的物化视图就需要编译，当处于这种状态的时候dba\_objects视图显示的STATUS为INVALID)

需要运行语句:ALTER MATERIALIZED VIEW MV\_NAME COMPILE;进行重新编译；

•UNUSABLE - Materialized view is not a read-consistent view of its masters from any point in time（物化视图引用的主表状态不确定）  
•UNKNOWN - Oracle Database does not know whether the materialized view is in a read-consistent view of its masters from any point in time (this is the case for materialized views created on prebuilt tables)（未知：通过prebuilt创建的表）  
•UNDEFINED - Materialized view has remote masters. The concept of staleness is not defined for such materialized views.（物化视图引用的表来自其他的数据库，一般通过dblink链接过来的）

二、实验测试：

2.1 创建物化视图

CREATE MATERIALIZED VIEW MV\_TEST (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)

TABLESPACE USERS

PCTUSED 0

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER\_POOL DEFAULT

FLASH\_CACHE DEFAULT

CELL\_FLASH\_CACHE DEFAULT

)

NOCACHE

LOGGING

NOCOMPRESS

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE USERS

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER\_POOL DEFAULT

FLASH\_CACHE DEFAULT

CELL\_FLASH\_CACHE DEFAULT

)

REFRESH FORCE ON DEMAND

WITH PRIMARY KEY

/\* Formatted on 2014/12/30 16:36:55 (QP5 v5.215.12089.38647) \*/

SELECT “EMP”.”EMPNO” “EMPNO”,

“EMP”.”ENAME” “ENAME”,

“EMP”.”JOB” “JOB”,

“EMP”.”MGR” “MGR”,

“EMP”.”HIREDATE” “HIREDATE”,

“EMP”.”SAL” “SAL”,

“EMP”.”COMM” “COMM”,

“EMP”.”DEPTNO” “DEPTNO”

FROM “SCOTT”.”EMP” “EMP”

WHERE “EMP”.”DEPTNO” = 20;

COMMENT ON MATERIALIZED VIEW MV\_TEST IS ‘snapshot table for snapshot SCOTT.MV\_TEST’;

CREATE UNIQUE INDEX PK\_EMP1 ON MV\_TEST

(EMPNO)

LOGGING

TABLESPACE USERS

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER\_POOL DEFAULT

FLASH\_CACHE DEFAULT

CELL\_FLASH\_CACHE DEFAULT

)

NOPARALLEL;

2.2 查询当前物化视图的状态

SQL> SELECT mview\_name,staleness FROM DBA\_MVIEWS WHERE MVIEW\_NAME=’MV\_TEST’;

MVIEW\_NAME STALENESS

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

MV\_TEST FRESH

2.3 查询数据库物化视图的状态

column OBJECT\_NAME format a20;

column STATUS format a20; OBJECT\_TYPE

column OBJECT\_TYPE format a20;

SQL> select OBJECT\_NAME,STATUS,OBJECT\_TYPE from dba\_objects where OBJECT\_NAME=’MV\_TEST’ ;

OBJECT\_NAME STATUS OBJECT\_TYPE

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

MV\_TEST VALID TABLE

MV\_TEST VALID MATERIALIZED VIEW

2.4 修改源表的数据

SQL> SELECT mview\_name,staleness FROM DBA\_MVIEWS WHERE MVIEW\_NAME=’MV\_TEST’;

MVIEW\_NAME STALENESS

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

MV\_TEST NEEDS\_COMPILE

显示状态需要编译

SQL> ALTER MATERIALIZED VIEW SCOTT.MV\_TEST COMPILE;

进行相应的编译

SQL> SELECT mview\_name,staleness FROM DBA\_MVIEWS WHERE MVIEW\_NAME=’MV\_TEST’;

MVIEW\_NAME STALENESS

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

MV\_TEST STALE

编译完成后，状态变成STALE

2.5 修改源表的表结构测试

SQL> ALTER TABLE SCOTT.EMP RENAME COLUMN COMM TO COMMS; //修改源表的结构

SQL> SELECT mview\_name,staleness FROM DBA\_MVIEWS WHERE MVIEW\_NAME=’MV\_TEST’;//查看物化视图的状态

MVIEW\_NAME STALENESS

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

MV\_TEST NEEDS\_COMPILE

SQL> ALTER MATERIALIZED VIEW SCOTT.MV\_TEST COMPILE; //重新编译

SQL> SELECT mview\_name,staleness FROM DBA\_MVIEWS WHERE MVIEW\_NAME=’MV\_TEST’; //重新编译状态没变；

MVIEW\_NAME STALENESS

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

MV\_TEST NEEDS\_COMPILE

SQL> select OBJECT\_NAME,STATUS,OBJECT\_TYPE from dba\_objects where OBJECT\_NAME=’MV\_TEST’ ;

OBJECT\_NAME STATUS OBJECT\_TYPE

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

MV\_TEST VALID TABLE

MV\_TEST INVALID MATERIALIZED VIEW

显示物化视图的状态INVALID

2.5 修改源表的结构跟物化视图一致

SQL> ALTER TABLE SCOTT.EMP RENAME COLUMN COMMS TO COMM; //修改源表的结构

SQL> SELECT mview\_name,staleness FROM DBA\_MVIEWS WHERE MVIEW\_NAME=’MV\_TEST’; //需要编译

MVIEW\_NAME STALENESS

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

MV\_TEST NEEDS\_COMPILE

SQL> ALTER MATERIALIZED VIEW SCOTT.MV\_TEST COMPILE; //进行重新编译

Materialized view altered.

SQL> SELECT mview\_name,staleness FROM DBA\_MVIEWS WHERE MVIEW\_NAME=’MV\_TEST’; //状态变成STALE

MVIEW\_NAME STALENESS

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

MV\_TEST STALE

SQL> select OBJECT\_NAME,STATUS,OBJECT\_TYPE from dba\_objects where OBJECT\_NAME=’MV\_TEST’ ; //状态变成VALID

OBJECT\_NAME STATUS OBJECT\_TYPE

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

MV\_TEST VALID TABLE

MV\_TEST VALID MATERIALIZED VIEW

总结：当物化视图的源表重新编译了，如果重建后的表结构没有发现变化，那么运行脚本ALTER MATERIALIZED VIEW MV\_NAME COMPILE后物化视图的状态就会刷新成有效的；

但是如果表的结构发生了变化，那么需要重新修改物化视图的脚本，相应的物化视图才能有效,dba\_objects显示出来的状态才是VALID的状态；

Oracle物化视图失效的几种情况及测试

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle物化视图

相关 Oracle创建物化视图

相关 Oracle-物化视图

相关 Oracle 物化视图之--复杂物化视图创建

相关 Oracle 物化视图

相关 Oracle 物化视图

相关 Oracle 物化视图使用

相关 Oracle物化视图失效的几种情况及测试

相关 Oracle物化视图与物化视图日志

相关 oracle 物化视图