No results found

【ORACLE】物化视图相关元数据视图字段说明

当需要对物化视图的状态等信息进行查询,监控和管理时,就要用到相关的系统元数据表了,本文介绍了Oracle物化视图相关的元数据系统视图的表结构,这些视图有:
ALL_VIEWS,DBA_MVIEWS,USER_MVIEWSALL_MVIEW_ANALYSISDBA_MVIEW_ANALYSISUSER_MVIEW_ANALYSISALL_MVIEW_AGGREGATESDBA_MVIEW_AGGREGATESUSER_MVIEW_AGGREGATESALL_MVIEW_REFRESH_TIMESDBA_MVIEW_REFRESH_TIMESUSER_MVIEW_REFRESH_TIMESALL_MVIEW_JOINSDBA_MVIEW_JOINSUSER_MVIEW_JOINSALL_MVIEW_KEYSDBA_MVIEW_KEYSUSER_MVIEW_KEYSALL_MVIEW_LOGSDBA_MVIEW_LOGSUSER_MVIEW_LOGS

ALL_MVIEWS

ALL_MVIEWS系统视图描述了当前用户下所有可以访问的物化视图的相关信息。
相关视图

  • DBA_MVIEWS描述了数据库中创建的所有物化视图的相关信息。
  • USER_MVIEWS描述了所属于当前用户下的所有物化视图的相关信息。
列名称数据类型是否可为NULL字段描述
OWNERVARCHAR2(30)NOT NULL创建物化视图所用的Schema.
MVIEW_NAMEVARCHAR2(30)NOT NULL物化视图名称
CONTAINER_NAMEVARCHAR2(30)NOT NULL存储物化视图数据的容器名称。一般跟MVIEWNAME是一样的。对于早于Oracle8i的版本,数据库附上了SNAP$的前缀。如果MVIEW_NAME超过里了19比特长度,数据库将会把名称截断至19比特,外加了4位的序列号以免发生名称重复。
QUERYLONG定义物化视图查询的语句
QUERY_LENNUMBER(38)定义的物化视图查询语句的长度,以比特为单位
UPDATABLEVARCHAR2(1)指明物化视图是(Y)否(N)可更新
UPDATE_LOGVARCHAR2(30)对于可更新的物化视图,更新日志的文件名称
MASTER_ROLLBACK_SEGVARCHAR2(30)Rollback segment for the master site or the master materialized view site
MASTER_LINKVARCHAR2(128)基表端的数据库链接
REWRITE_ENABLEDVARCHAR2(1)指出是(Y)否(N)支持物化视图的查询重写
REWRITE_CAPABILITYVARCHAR2(9)指出物化视图是否适合查询重写。会有以下三种情况:
NONE:物化视图不能被用于查询重写以为查询重写选项被禁用了。
TEXTMATCH:物化视图的查询语句中包含查询重写的相关限制。
GENETAL:物化视图的查询语句中没有任何限制,所以数据库将在物化视图上应用支持的查询重写功能。
REFRESH_MODEVARCHAR2(6)物化视图的刷新模式:
DEMAND:物化视图将在调用刷新物化视图的存储过程时刷新。
COMMIT:物化视图将在基表上的事物提交后刷新。
NEVER:物化视图永远不会被刷新。
REFRESH_METHODVARCHAR2(8)物化视图的刷新方法:
COMPLETE:物化视图会对基表的数据进行全量刷新。
FORCE:数据库引擎自动判断,如果物化视图可被增量刷新则执行增量刷新,否则全量刷新。
FAST:物化视图将从上一次刷新后,对于基表的数据变化进行增量刷新。
NEVER:用户告诉数据库该物化视图不用刷新。
BUILD_MODEVARCHAR2(9)指明物化视图在创建时是否填充数据:
IMMEDIATE:在创建物化视图时填充数据。
DEFERRED:在创建时不填充数据,之后用户要手动更新数据。
PREBUILT:在创建物化视图时用一个已经存在的表的数据填充物化视图。
FAST_REFRESHABLEVARCHAR2(18)指明物化视图是否适合增量刷新。Oracle数据库会基于定义物化视图的查询语句静态计算这个值。
NO:物化视图不支持增量刷新。
DML;只支持DML操作的增量刷新。
DIRLOAD_DML;适合于直接装载和DML操作的增加刷新。
DIRLOAD_LIMITEDDML;只支持直接装载和某些类型的DML操作的增量刷新。
LAST_REFRESH_TYPEVARCHAR2(8)最近一次刷新方法:COMPLETE:全量刷新。FAST:增量刷新。NA:还没有刷新过,比如创建时没指定填充数据的时候。
LAST_REFRESH_DATEDATE最近一次刷新的日期时间,如果从没刷新过就是空。
STALENESSVARCHAR2(19)物化视图的数据和基表数据的关系:
FRESH:物化视图数据跟基表的数据是一致的。
STALE:物化视图过期了,因为有一个或多个基表的数据已经变了,如果物化视图在此之前是FRESH,然后才变成STALE,那物化视图的数据跟基表表数据变更前是一致的。
NEEDS_COMPILE:物化视图的某些基表已经改了,要用ALTER MATERIALIZED VIEW…COMPILE语句来重新计算物化视图该状态。
UNUSABLE:物化视图的数据跟基表的数据任何时间都不一致。
UNKNOWN:Oracle数据库无法得知物化视图的数据跟基表的关系。这可能是基于prebuilt table来创建的物化视图。
UNDEFINED:物化视图有远程的基表,对于这类物化视图该状态不适用。
AFTER_FAST_REFRESHVARCHAR2(19)指明在物化视图执行增量刷新后是啥状态,状态值跟STALENESS列是一样的,只是多了一个NA,当执行增量刷新后变为不适用增量了出现这个值。
UNKNOWN_PREBUILTVARCHAR2(1)Indicates whether the materialized view is prebuilt (Y) or not (N)
UNKNOWN_PLSQL_FUNCVARCHAR2(1)Indicates whether the materialized view contains PL/SQL functions (Y) or not (N)
UNKNOWN_EXTERNAL_TABLEVARCHAR2(1)Indicates whether the materialized view contains external tables (Y) or not (N)
UNKNOWN_CONSIDER_FRESHVARCHAR2(1)Indicates whether the materialized view is considered fresh (Y) or not (N)
UNKNOWN_IMPORTVARCHAR2(1)Indicates whether the materialized view is imported (Y) or not (N)
UNKNOWN_TRUSTED_FDVARCHAR2(1)Indicates whether the materialized view uses trusted constraints for refresh (Y) or not (N)
COMPILE_STATEVARCHAR2(19)检查物化视图的相关基表都是否合法:
VALID:物化视图是没问题的,物化视图的基表没有变更。
NEEDS_COMPILE:物化视图的某些基表被改变了,要重新编译。
ERROR:物化视图有错误。
USE_NO_INDEXVARCHAR2(1)指出创建物化视图的时候是否使用USING NO INDEX子句,或者物化视图创建时默认了索引。
STALE_SINCEDATETime from when the materialized view became stale
NUM_PCT_TABLESNUMBERNumber of PCT detail tables
NUM_FRESH_PCT_REGIONSNUMBERNumber of fresh PCT partition regions
NUM_STALE_PCT_REGIONSNUMBERNumber 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.

ColumnDatatypeNULLDescription
OWNERVARCHAR2(30)NOT NULLOwner of the materialized view
MVIEW_NAMEVARCHAR2(30)NOT NULLName of the materialized view
MVIEW_TABLE_OWNERVARCHAR2(30)NOT NULLOwner of the container table (see next column)
CONTAINER_NAMEVARCHAR2(30)Name of the internal container in which the materialized view data is held. Normally this is the same asMVIEWNAME. 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_SCNNUMBERSystem change number (SCN) of the last refresh operation
LAST_REFRESH_DATEDATESYSDATE?of the last refresh
REFRESH_METHODVARCHAR2(8)Default refresh method:
FORCE
FAST
COMPLETE
NEVER
SUMMARYVARCHAR2(1)Indicates whether this materialized view includes a?GROUP BY?clause or aggregation (Y) or not (N)
FULLREFRESHTIMNUMBERApproximate refresh time, in seconds, for full refresh (defined only when?SUMMARY?=?Y)
INCREFRESHTIMNUMBERApproximate refresh time, in seconds, for fast refresh (defined only when?SUMMARY?=?Y)
CONTAINS_VIEWSVARCHAR2(1)Indicates whether this materialized view contains a view in its definition (Y) or not (N)
UNUSABLEVARCHAR2(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_SYNTAXVARCHAR2(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_REFRESHABLEVARCHAR2(1)Indicates whether this materialized view can be fast refreshed (Y) or not (N)
KNOWN_STALEVARCHAR2(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)
INVALIDVARCHAR2(1)Indicates whether this materialized view is in an invalid state (inconsistent metadata) (Y) or not (N)
REWRITE_ENABLEDVARCHAR2(1)Indicates whether this materialized view is currently enabled for query rewrite (Y) or not (N)
QUERY_LENNUMBERLength (in bytes) of the query field
QUERYLONGSELECT?expression of the materialized view definition
REVISIONNUMBERNOT NULLReserved 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.

ColumnDatatypeNULLDescription
OWNERVARCHAR2(30)NOT NULLOwner of the materialized view
MVIEW_NAMEVARCHAR2(30)NOT NULLName of the materialized view
POSITION_IN_SELECTNUMBERNOT NULLOrdinal position of this aggregation within the?SELECT?list. For the position of nonaggregate elements of the select list, see”ALL_MVIEW_KEYS”.
CONTAINER_COLUMNVARCHAR2(30)NOT NULLName of this column in the container table
AGG_FUNCTIONVARCHAR2(8)Aggregation function
DISTINCTFLAGVARCHAR2(1)Indicates whether this aggregation is distinct (Y) or not (N)
MEASURELONGSQL 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.

ColumnDatatypeNULLDescription
OWNERVARCHAR2(30)NOT NULLOwner of the materialized view
NAMEVARCHAR2(30)NOT NULLName of the materialized view
MASTER_OWNERVARCHAR2(30)Owner of the master table
MASTERVARCHAR2(30)Name of the master table
LAST_REFRESHDATESYSDATE?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.

ColumnDatatypeNULLDescription
OWNERVARCHAR2(30)NOT NULLOwner of the materialized view
MVIEW_NAMEVARCHAR2(30)NOT NULLMaterialized view name
DETAILOBJ1_OWNERVARCHAR2(30)NOT NULLOwner of the first object in the joinFoot?1?
DETAILOBJ1_RELATIONVARCHAR2(30)NOT NULLName of the first object in the joinFootref?1
DETAILOBJ1_COLUMNVARCHAR2(30)NOT NULLJoin column of the first object in the joinFootref?1
OPERATORCHAR(1)Join operatorFootref?1
OPERATOR_TYPEVARCHAR2(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_OWNERVARCHAR2(30)NOT NULLOwner of the second object in the joinFootref?1
DETAILOBJ2_RELATIONVARCHAR2(30)NOT NULLName of the second object in the joinFootref?1
DETAILOBJ2_COLUMNVARCHAR2(30)NOT NULLJoin 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.

ColumnDatatypeNULLDescription
OWNERVARCHAR2(30)NOT NULLOwner of the materialized view
MVIEW_NAMEVARCHAR2(30)NOT NULLMaterialized view name
POSITION_IN_SELECTNUMBERNOT NULLOrdinal position of this key within the?SELECT?list
CONTAINER_COLUMNVARCHAR2(30)NOT NULLName of the column in the container table
DETAILOBJ_OWNERVARCHAR2(30)NOT NULLDetail object owner
DETAILOBJ_NAMEVARCHAR2(30)NOT NULLDetail object name (for example, the name of a table or view)
DETAILOBJ_ALIASVARCHAR2(30)Implicit or explicit alias for detail relation
DETAILOBJ_TYPEVARCHAR2(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.

ColumnDatatypeNULLDescription
LOG_OWNERVARCHAR2(30)Owner of the materialized view log
MASTERVARCHAR2(30)Name of the master table or master materialized view whose changes are logged
LOG_TABLEVARCHAR2(30)Name of the table where the changes to the master table or master materialized view are logged
LOG_TRIGGERVARCHAR2(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.
ROWIDSVARCHAR2(3)Indicates whether rowid information is recorded (YES) or not (NO)
PRIMARY_KEYVARCHAR2(3)Indicates whether primary key information is recorded (YES) or not (NO)
OBJECT_IDVARCHAR2(3)Indicates whether object identifier information in an object table is recorded (YES) or not (NO)
FILTER_COLUMNSVARCHAR2(3)Indicates whether filter column information is recorded (YES) or not (NO)
SEQUENCEVARCHAR2(3)Indicates whether the sequence value, which provides additional ordering information, is recorded (YES) or not (NO)
INCLUDE_NEW_VALUESVARCHAR2(3)Indicates whether both old and new values are recorded (YES) or old values are recorded but new values are not recorded (NO)
PURGE_ASYNCHRONOUSVARCHAR2(3)Indicates whether the materialized view log is purged asynchronously (YES) or not (NO)
PURGE_DEFERREDVARCHAR2(3)Indicates whether the materialized view log is purged in a deferred manner (YES) or not (NO)
PURGE_STARTDATEFor deferred purge, the purge start date
PURGE_INTERVALVARCHAR2(200)For deferred purge, the purge interval
LAST_PURGE_DATEDATEDate of the last purge
LAST_PURGE_STATUSNUMBERStatus of the last purge (error code or?0?for success)
NUM_ROWS_PURGEDNUMBERNumber of rows purged in the last purge
COMMIT_SCN_BASEDVARCHAR2(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

文章目录
  1. 1. ALL_MVIEWS
  2. 2. ALL_MVIEW_ANALYSIS
  3. 3. ALL_MVIEW_AGGREGATES
  4. 4. ALL_MVIEW_REFRESH_TIMES
  5. 5. ALL_MVIEW_JOINS
  6. 6. ALL_MVIEW_KEYS
  7. 7. ALL_MVIEW_LOGS
|