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

说明：物化视图（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 USERSPCTUSED 0PCTFREE 10INITRANS 2MAXTRANS 255STORAGE （ INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT）NOCACHELOGGINGNOCOMPRESSNOPARALLELBUILD IMMEDIATEUSING 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 DEMANDWITH PRIMARY KEYAS /* 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）LOGGINGTABLESPACE USERSPCTFREE 10INITRANS 2MAXTRANS 255STORAGE （ 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_TYPEcolumn 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 TABLEMV_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编译完成后，状态变成STALE2.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 TABLEMV_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"; //状态变成STALEMVIEW_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 TABLEMV_TEST VALID MATERIALIZED VIEW 总结：当物化视图的源表重新编译了，如果重建后的表结构没有发现变化，那么运行脚本ALTER MATERIALIZED VIEW MV_NAME COMPILE后物化视图的状态就会刷新成有效的；但是如果表的结构发生了变化，那么需要重新修改物化视图的脚本，相应的物化视图才能有效,dba_objects显示出来的状态才是VALID的状态；物化视图定义不当引发Oracle性能问题 http://www.linuxidc.com/Linux/2014-02/97083.htmOracle物化视图测试 http://www.linuxidc.com/Linux/2013-11/93185.htmOracle【定期刷新】的【基于连接】的物化视图 http://www.linuxidc.com/Linux/2013-11/92863.htmOracle物化视图创建报ORA-00942错误解决 http://www.linuxidc.com/Linux/2013-05/85072.htmOracle 存储过程刷新物化视图 http://www.linuxidc.com/Linux/2013-03/81776.htmOracle物化视图的使用 http://www.linuxidc.com/Linux/2013-02/78870.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址