说明：物化视图（Materialized Views）是查询的结果集，所有原表和视图的变更都将导致物化视图的失效，最近就发现了一个物化视图经常失效的问题，以下是整理的文档。
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
AS?
/* 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';
MVI