当需要对物化视图的状态等信息进行查询，监控和管理时，就要用到相关的系统元数据表了，本文介绍了Oracle物化视图相关的元数据系统视图的表结构，这些视图有：ALL_VIEWS,DBA_MVIEWS,USER_MVIEWS，ALL_MVIEW_ANALYSIS，DBA_MVIEW_ANALYSIS，USER_MVIEW_ANALYSIS，ALL_MVIEW_AGGREGATES，DBA_MVIEW_AGGREGATES，USER_MVIEW_AGGREGATES，ALL_MVIEW_REFRESH_TIMES，DBA_MVIEW_REFRESH_TIMES，USER_MVIEW_REFRESH_TIMES，ALL_MVIEW_JOINS，DBA_MVIEW_JOINS，USER_MVIEW_JOINS，ALL_MVIEW_KEYS，DBA_MVIEW_KEYS，USER_MVIEW_KEYS，ALL_MVIEW_LOGS，DBA_MVIEW_LOGS，USER_MVIEW_LOGS。
ALL_MVIEWS
ALL_MVIEWS系统视图描述了当前用户下所有可以访问的物化视图的相关信息。
相关视图
DBA_MVIEWS描述了数据库中创建的所有物化视图的相关信息。USER_MVIEWS描述了所属于当前用户下的所有物化视图的相关信息。
| 列名称 | 数据类型 | 是否可为NULL | 字段描述 |
|---|---|---|---|
| OWNER | VARCHAR2(30) | NOT NULL | 创建物化视图所用的Schema. |
| MVIEW_NAME | VARCHAR2(30) | NOT NULL | 物化视图名称 |
| CONTAINER_NAME | VARCHAR2(30) | NOT NULL | 存储物化视图数据的容器名称。一般跟MVIEWNAME是一样的。对于早于Oracle8i的版本，数据库附上了SNAP$的前缀。如果MVIEW_NAME超过里了19比特长度，数据库将会把名称截断至19比特，外加了4位的序列号以免发生名称重复。 |
| QUERY | LONG | 定义物化视图查询的语句 | |
| QUERY_LEN | NUMBER(38) | 定义的物化视图查询语句的长度，以比特为单位 | |
| UPDATABLE | VARCHAR2(1) | 指明物化视图是(Y)否(N)可更新 | |
| UPDATE_LOG | VARCHAR2(30) | 对于可更新的物化视图，更新日志的文件名称 | |
| MASTER_ROLLBACK_SEG | VARCHAR2(30) | Rollback segment for the master site or the master materialized view site | |
| MASTER_LINK | VARCHAR2(128) | 基表端的数据库链接 | |
| REWRITE_ENABLED | VARCHAR2(1) | 指出是(Y)否(N)支持物化视图的查询重写 | |
| REWRITE_CAPABILITY | VARCHAR2(9) | 指出物化视图是否适合查询重写。会有以下三种情况： NONE：物化视图不能被用于查询重写以为查询重写选项被禁用了。 TEXTMATCH：物化视图的查询语句中包含查询重写的相关限制。 GENETAL：物化视图的查询语句中没有任何限制，所以数据库将在物化视图上应用支持的查询重写功能。 | |
| REFRESH_MODE | VARCHAR2(6) | 物化视图的刷新模式： DEMAND：物化视图将在调用刷新物化视图的存储过程时刷新。 COMMIT：物化视图将在基表上的事物提交后刷新。 NEVER：物化视图永远不会被刷新。 | |
| REFRESH_METHOD | VARCHAR2(8) | 物化视图的刷新方法： COMPLETE：物化视图会对基表的数据进行全量刷新。 FORCE：数据库引擎自动判断，如果物化视图可被增量刷新则执行增量刷新，否则全量刷新。 FAST：物化视图将从上一次刷新后，对于基表的数据变化进行增量刷新。 NEVER：用户告诉数据库该物化视图不用刷新。 | |
| BUILD_MODE | VARCHAR2(9) | 指明物化视图在创建时是否填充数据： IMMEDIATE：在创建物化视图时填充数据。 DEFERRED：在创建时不填充数据，之后用户要手动更新数据。 PREBUILT：在创建物化视图时用一个已经存在的表的数据填充物化视图。 | |
| FAST_REFRESHABLE | VARCHAR2(18) | 指明物化视图是否适合增量刷新。Oracle数据库会基于定义物化视图的查询语句静态计算这个值。 NO：物化视图不支持增量刷新。 DML；只支持DML操作的增量刷新。 DIRLOAD_DML；适合于直接装载和DML操作的增加刷新。 DIRLOAD_LIMITEDDML；只支持直接装载和某些类型的DML操作的增量刷新。 | |
| LAST_REFRESH_TYPE | VARCHAR2(8) | 最近一次刷新方法：COMPLETE：全量刷新。FAST：增量刷新。NA：还没有刷新过，比如创建时没指定填充数据的时候。 | |
| LAST_REFRESH_DATE | DATE | 最近一次刷新的日期时间，如果从没刷新过就是空。 | |
| STALENESS | VARCHAR2(19) | 物化视图的数据和基表数据的关系： FRESH：物化视图数据跟基表的数据是一致的。 STALE：物化视图过期了，因为有一个或多个基表的数据已经变了，如果物化视图在此之前是FRESH，然后才变成STALE，那物化视图的数据跟基表表数据变更前是一致的。 NEEDS_COMPILE：物化视图的某些基表已经改了，要用ALTER MATERIALIZED VIEW…COMPILE语句来重新计算物化视图该状态。 UNUSABLE：物化视图的数据跟基表的数据任何时间都不一致。 UNKNOWN：Oracle数据库无法得知物化视图的数据跟基表的关系。这可能是基于prebuilt table来创建的物化视图。 UNDEFINED：物化视图有远程的基表，对于这类物化视图该状态不适用。 | |
| AFTER_FAST_REFRESH | VARCHAR2(19) | 指明在物化视图执行增量刷新后是啥状态，状态值跟STALENESS列是一样的，只是多了一个NA，当执行增量刷新后变为不适用增量了出现这个值。 | |
| UNKNOWN_PREBUILT | VARCHAR2(1) | Indicates whether the materialized view is prebuilt (Y) or not (N) | |
| UNKNOWN_PLSQL_FUNC | VARCHAR2(1) | Indicates whether the materialized view contains PL/SQL functions (Y) or not (N) | |
| UNKNOWN_EXTERNAL_TABLE | VARCHAR2(1) | Indicates whether the materialized view contains external tables (Y) or not (N) | |
| UNKNOWN_CONSIDER_FRESH | VARCHAR2(1) | Indicates whether the materialized view is considered fresh (Y) or not (N) | |
| UNKNOWN_IMPORT | VARCHAR2(1) | Indicates whether the materialized view is imported (Y) or not (N) | |
| UNKNOWN_TRUSTED_FD | VARCHAR2(1) | Indicates whether the materialized view uses trusted constraints for refresh (Y) or not (N) | |
| COMPILE_STATE | VARCHAR2(19) | 检查物化视图的相关基表都是否合法： VALID：物化视图是没问题的，物化视图的基表没有变更。 NEEDS_COMPILE：物化视图的某些基表被改变了，要重新编译。 ERROR：物化视图有错误。 | |
| USE_NO_INDEX | VARCHAR2(1) | 指出创建物化视图的时候是否使用USING NO INDEX子句，或者物化视图创建时默认了索引。 | |
| STALE_SINCE | DATE | Time from when the materialized view became stale | |
| NUM_PCT_TABLES | NUMBER | Number of PCT detail tables | |
| NUM_FRESH_PCT_REGIONS | NUMBER | Number of fresh PCT partition regions | |
| NUM_STALE_PCT_REGIONS | NUMBER | Number of stale PCT partition regions |
参考https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1153.htm#REFRN20139
ALL_MVIEW_ANALYSIS
ALL_MVIEW_ANALYSIS describes the materialized views accessible to the current user. It provides additional information for analysis by applications. Minimal information is displayed for materialized views that do not support query rewrite (such as materialized views with remote master tables or nondeterministic functions).
Related Views
● DBA_MVIEW_ANALYSIS describes all such materialized views in the database.
● USER_MVIEW_ANALYSIS describes all such materialized views owned by the current user.
Note:
All of the information in these views is also displayed in ALL_MVIEWS and its related views. Oracle recommends that you refer to ALL_MVIEWSfor this information instead of these views.
| Column | Datatype | NULL | Description |
|---|---|---|---|
| OWNER | VARCHAR2(30) | NOT NULL | Owner of the materialized view |
| MVIEW_NAME | VARCHAR2(30) | NOT NULL | Name of the materialized view |
| MVIEW_TABLE_OWNER | VARCHAR2(30) | NOT NULL | Owner of the container table (see next column) |
| CONTAINER_NAME | VARCHAR2(30) | Name of the internal container in which the materialized view data is held. Normally this is the same asMVIEW_NAME. For materialized views created prior to Oracle8i, Oracle Database attaches the 6-byte prefix?SNAP$_. If?MVIEW_NAMEhas more than 19 bytes, then Oracle Database truncates the name to 19 bytes and adds a 4-byte sequence number as a suffix to produce a nonambiguousCONTAINER_NAME. | |
| LAST_REFRESH_SCN | NUMBER | System change number (SCN) of the last refresh operation | |
| LAST_REFRESH_DATE | DATE | SYSDATE?of the last refresh | |
| REFRESH_METHOD | VARCHAR2(8) | Default refresh method: FORCE FAST COMPLETE NEVER | |
| SUMMARY | VARCHAR2(1) | Indicates whether this materialized view includes a?GROUP BY?clause or aggregation (Y) or not (N) | |
| FULLREFRESHTIM | NUMBER | Approximate refresh time, in seconds, for full refresh (defined only when?SUMMARY?=?Y) | |
| INCREFRESHTIM | NUMBER | Approximate refresh time, in seconds, for fast refresh (defined only when?SUMMARY?=?Y) | |
| CONTAINS_VIEWS | VARCHAR2(1) | Indicates whether this materialized view contains a view in its definition (Y) or not (N) | |
| UNUSABLE | VARCHAR2(1) | Indicates whether this materialized view is?UNUSABLE?(inconsistent data) (Y) or not (N). A materialized view can be?UNUSABLE?if a system failure occurs during a full refresh. | |
| RESTRICTED_SYNTAX | VARCHAR2(1) | Indicates whether this materialized view had a restriction in its defining query that limits the use of query rewrite (Y) or not (N). More complete information is provided by theREWRITE_CAPABILITY?column of the?*_MVIEWS?view. | |
| INC_REFRESHABLE | VARCHAR2(1) | Indicates whether this materialized view can be fast refreshed (Y) or not (N) | |
| KNOWN_STALE | VARCHAR2(1) | Indicates whether the data contained in the materialized view is known to be inconsistent with the master table data because that has been updated since the last successful refresh (Y) or not (N) | |
| INVALID | VARCHAR2(1) | Indicates whether this materialized view is in an invalid state (inconsistent metadata) (Y) or not (N) | |
| REWRITE_ENABLED | VARCHAR2(1) | Indicates whether this materialized view is currently enabled for query rewrite (Y) or not (N) | |
| QUERY_LEN | NUMBER | Length (in bytes) of the query field | |
| QUERY | LONG | SELECT?expression of the materialized view definition | |
| REVISION | NUMBER | NOT NULL | Reserved for internal use |
参考
https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1144.htm#REFRN20132
ALL_MVIEW_AGGREGATES
ALL_MVIEW_AGGREGATES describes the grouping functions (aggregate operations) that appear in the SELECT list of materialized aggregate views accessible to the current user.
Related Views
● DBA_MVIEW_AGGREGATES describes all such grouping functions defined for all materialized views in the database.
● USER_MVIEW_AGGREGATES describes all such grouping functions defined for all materialized views owned by the current user.
Note:
All three views exclude materialized views that reference remote tables or that include references to a nonstatic value such as SYSDATE orUSER. These views also exclude materialized views that were created as “snapshots” prior to Oracle8i and that were never altered to enable query rewrite.
| Column | Datatype | NULL | Description |
|---|---|---|---|
| OWNER | VARCHAR2(30) | NOT NULL | Owner of the materialized view |
| MVIEW_NAME | VARCHAR2(30) | NOT NULL | Name of the materialized view |
| POSITION_IN_SELECT | NUMBER | NOT NULL | Ordinal position of this aggregation within the?SELECT?list. For the position of nonaggregate elements of the select list, see"ALL_MVIEW_KEYS". |
| CONTAINER_COLUMN | VARCHAR2(30) | NOT NULL | Name of this column in the container table |
| AGG_FUNCTION | VARCHAR2(8) | Aggregation function | |
| DISTINCTFLAG | VARCHAR2(1) | Indicates whether this aggregation is distinct (Y) or not (N) | |
| MEASURE | LONG | SQL text of the measure, excluding the aggregation function. Equal to?*?for?COUNT(*). |
参考
https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1143.htm#REFRN20131
ALL_MVIEW_REFRESH_TIMES
ALL_MVIEW_REFRESH_TIMES describes refresh times of the materialized views accessible to the current user.
Related Views
● DBA_MVIEW_REFRESH_TIMES describes refresh times of all materialized views in the database.
● USER_MVIEW_REFRESH_TIMES describes refresh times of the materialized views owned by the current user.
| Column | Datatype | NULL | Description |
|---|---|---|---|
| OWNER | VARCHAR2(30) | NOT NULL | Owner of the materialized view |
| NAME | VARCHAR2(30) | NOT NULL | Name of the materialized view |
| MASTER_OWNER | VARCHAR2(30) | Owner of the master table | |
| MASTER | VARCHAR2(30) | Name of the master table | |
| LAST_REFRESH | DATE | SYSDATE?from the master site at the time of the last refresh |
ALL_MVIEW_JOINS
ALL_MVIEW_JOINS describes joins between two columns in the WHERE clause of the subquery that defines a materialized view accessible to the current user.
Related Views
● DBA_MVIEW_JOINS describes all such joins for all materialized views in the database.
● USER_MVIEW_JOINS describes such joins for all materialized views owned by the current user.
Note:
All three views exclude materialized views that reference remote tables or that includes references to a nonstatic value such as SYSDATE orUSER. These views also exclude materialized views that were created as “snapshots” prior to Oracle8i and that were never altered to enable query rewrite.
| Column | Datatype | NULL | Description |
|---|---|---|---|
| OWNER | VARCHAR2(30) | NOT NULL | Owner of the materialized view |
| MVIEW_NAME | VARCHAR2(30) | NOT NULL | Materialized view name |
| DETAILOBJ1_OWNER | VARCHAR2(30) | NOT NULL | Owner of the first object in the joinFoot?1? |
| DETAILOBJ1_RELATION | VARCHAR2(30) | NOT NULL | Name of the first object in the joinFootref?1 |
| DETAILOBJ1_COLUMN | VARCHAR2(30) | NOT NULL | Join column of the first object in the joinFootref?1 |
| OPERATOR | CHAR(1) | Join operatorFootref?1 | |
| OPERATOR_TYPE | VARCHAR2(1) | Indicates whether the join is an inner join (I) or the?DETAILOBJ1table is the left side of an outer join (L)Footref?1 | |
| DETAILOBJ2_OWNER | VARCHAR2(30) | NOT NULL | Owner of the second object in the joinFootref?1 |
| DETAILOBJ2_RELATION | VARCHAR2(30) | NOT NULL | Name of the second object in the joinFootref?1 |
| DETAILOBJ2_COLUMN | VARCHAR2(30) | NOT NULL | Join column of the second object in the joinFootref?1 |
Footnote 1 These rows relate only to materialized join views and materialized aggregate views. They describe the two detail objects of a materialized view join.
参考
https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1149.htm#REFRN20134
ALL_MVIEW_KEYS
ALL_MVIEW_KEYS describes the columns or expressions in the SELECT list upon which materialized views accessible to the current user are based.
Related Views
● DBA_MVIEW_KEYS describes such columns and expressions for all materialized views in the database.
● USER_MVIEW_KEYS describes such columns and expressions for all materialized views owned by the current user.
Note:
All three views exclude materialized views that reference remote tables or that includes references to a nonstatic value such as SYSDATE orUSER. These views also exclude materialized views that were created as snapshots prior to Oracle8i and that were never altered to enable query rewrite.
| Column | Datatype | NULL | Description |
|---|---|---|---|
| OWNER | VARCHAR2(30) | NOT NULL | Owner of the materialized view |
| MVIEW_NAME | VARCHAR2(30) | NOT NULL | Materialized view name |
| POSITION_IN_SELECT | NUMBER | NOT NULL | Ordinal position of this key within the?SELECT?list |
| CONTAINER_COLUMN | VARCHAR2(30) | NOT NULL | Name of the column in the container table |
| DETAILOBJ_OWNER | VARCHAR2(30) | NOT NULL | Detail object owner |
| DETAILOBJ_NAME | VARCHAR2(30) | NOT NULL | Detail object name (for example, the name of a table or view) |
| DETAILOBJ_ALIAS | VARCHAR2(30) | Implicit or explicit alias for detail relation | |
| DETAILOBJ_TYPE | VARCHAR2(5) | Detail object type: |
参考
https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1150.htm#REFRN20135
ALL_MVIEW_LOGS
ALL_MVIEW_LOGS describes all materialized view logs accessible to the current user.
Related Views
● DBA_MVIEW_LOGS describes all materialized view logs in the database.
● USER_MVIEW_LOGS describes all materialized view logs owned by the current user.
| Column | Datatype | NULL | Description |
|---|---|---|---|
| LOG_OWNER | VARCHAR2(30) | Owner of the materialized view log | |
| MASTER | VARCHAR2(30) | Name of the master table or master materialized view whose changes are logged | |
| LOG_TABLE | VARCHAR2(30) | Name of the table where the changes to the master table or master materialized view are logged | |
| LOG_TRIGGER | VARCHAR2(30) | Obsolete with Oracle8i?and later. Set to NULL. Formerly, this parameter was an after-row trigger on the master which inserted rows into the log. | |
| ROWIDS | VARCHAR2(3) | Indicates whether rowid information is recorded (YES) or not (NO) | |
| PRIMARY_KEY | VARCHAR2(3) | Indicates whether primary key information is recorded (YES) or not (NO) | |
| OBJECT_ID | VARCHAR2(3) | Indicates whether object identifier information in an object table is recorded (YES) or not (NO) | |
| FILTER_COLUMNS | VARCHAR2(3) | Indicates whether filter column information is recorded (YES) or not (NO) | |
| SEQUENCE | VARCHAR2(3) | Indicates whether the sequence value, which provides additional ordering information, is recorded (YES) or not (NO) | |
| INCLUDE_NEW_VALUES | VARCHAR2(3) | Indicates whether both old and new values are recorded (YES) or old values are recorded but new values are not recorded (NO) | |
| PURGE_ASYNCHRONOUS | VARCHAR2(3) | Indicates whether the materialized view log is purged asynchronously (YES) or not (NO) | |
| PURGE_DEFERRED | VARCHAR2(3) | Indicates whether the materialized view log is purged in a deferred manner (YES) or not (NO) | |
| PURGE_START | DATE | For deferred purge, the purge start date | |
| PURGE_INTERVAL | VARCHAR2(200) | For deferred purge, the purge interval | |
| LAST_PURGE_DATE | DATE | Date of the last purge | |
| LAST_PURGE_STATUS | NUMBER | Status of the last purge (error code or?0?for success) | |
| NUM_ROWS_PURGED | NUMBER | Number of rows purged in the last purge | |
| COMMIT_SCN_BASED | VARCHAR2(3) | Indicates whether the materialized view log is commit SCN-based (YES) or not (NO) |
参考
https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1151.htm#REFRN20137
