【ORACLE】物化檢視相關後設資料檢視欄位說明
當需要對物化檢視的狀態等資訊進行查詢，監控和管理時，就要用到相關的系統後設資料表了，本文介紹了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
相關文章
- 【ORACLE】常用物化檢視相關後設資料查詢語句Oracle
- MongoDB檢視執行計劃方法及相關欄位說明MongoDB
- Oracle 物化檢視 快速重新整理 限制 說明Oracle
- Oracle相關資料字典檢視Oracle
- 物化檢視相關工具包
- oracle物化檢視Oracle
- oracle 建立物化檢視Oracle
- Oracle 物化檢視建立Oracle
- oracle資料庫dba_hist等檢視中的Delta相關欄位介紹Oracle資料庫
- 資料複製_物化檢視
- 資料庫的物化檢視資料庫
- oracle Metrics相關檢視Oracle
- 物化檢視日誌表被DROP後建立物化檢視報錯
- Oracle普通檢視和物化檢視的區別Oracle
- oracle 常用檢視 簡短說明Oracle
- Oracle物化檢視詳解Oracle
- ORACLE物化檢視測試Oracle
- Oracle 物化檢視案例分享Oracle
- Oracle物化檢視語法Oracle
- ORACLE物化檢視入門Oracle
- oracle物化檢視系列(二)Oracle
- oracle物化檢視系列(一)Oracle
- Linux 檢視核心版本命令的相關說明Linux
- 物化檢視妙用__表同步使用物化檢視方法
- 【物化檢視】幾種物化檢視日誌分析
- Oracle資料庫中物化檢視的原理剖析Oracle資料庫
- 檢視oracle鎖相關資訊Oracle
- Oracle 物化檢視 詳細錯誤描述 檢視方法Oracle
- Oracle如何根據物化檢視日誌快速重新整理物化檢視Oracle
- 【物化檢視】根據物化檢視日誌快速重新整理物化檢視的過程
- oracle-一些檢視效能相關的檢視Oracle
- CUUG oracle物化檢視講解Oracle
- Oracle物化檢視3 - Prebuilt MVOracleUI
- Oracle 物化檢視 例項一Oracle
- Oracle物化檢視及SnapshotOracle
- ORACLE中的物化檢視建立Oracle
- (轉)Sql Server 快速檢視錶結構(表描述及欄位說明)SQLServer
- 物化檢視--資料倉儲手冊