说明：物化视图（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
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';
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物化视图创建报ora-00942错误解决
oracle 存储过程刷新物化视图
oracle物化视图的使用
本文永久更新链接地址：