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 wasFRESHbefore it becameSTALE, 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. AnALTER MATERIALIZED VIEW...COMPILEstatement 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 co