Note
This view was introduced in V4.3.0.
Description
The DBA_MVIEWS view displays information about all materialized views in the database.
Field description
Field name |
Type |
NULLable |
Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | YES | The name of the owner of the materialized view. |
| MVIEW_NAME | VARCHAR2(128) | YES | The name of the materialized view. |
| CONTAINER_NAME | VARCHAR2(128) | YES | The name of the container table of the materialized view. |
| QUERY | CLOB | NO | The query that defines the materialized view. |
| QUERY_LEN | NUMBER | NO | The length of the query, in bytes. |
| UPDATABLE | VARCHAR2(1) | NO | Indicates whether the materialized view is updatable.
NoteThe field is compatible only and its default value is N. |
| UPDATE_LOG | VARCHAR2(128) | NO | The name of the update log file for updatable materialized views.
NoteThe field is compatible only and its default value is NULL. |
| MASTER_ROLLBACK_SEG | VARCHAR2(128) | NO | The rollback segment of the primary site or primary materialized view site.
NoteThe field is compatible only and its default value is NULL. |
| MASTER_LINK | VARCHAR2(128) | NO | The database link of the primary site or primary materialized view site.
NoteThe field is compatible only and its default value is NULL. |
| REWRITE_ENABLED | VARCHAR2(1) | NO | Specifies whether to enable query rewriting.
|
| REWRITE_CAPABILITY | VARCHAR2(9) | NO | Specifies whether the materialized view is eligible for query rewriting. If yes, the materialized view must follow the following rules:
NoteThe field is compatible only and its default value is NULL. |
| REFRESH_MODE | VARCHAR2(6) | NO | The refresh mode of the materialized view.
|
| REFRESH_METHOD | VARCHAR2(8) | NO | The default refresh method for refreshing the materialized view (can be overridden through an API).
|
| BUILD_MODE | VARCHAR2(9) | NO | Specifies how to fill the materialized view during creation.
|
| FAST_REFRESHABLE | VARCHAR2(18) | NO | Specifies whether the materialized view is suitable for incremental (fast) refreshes.
NoteThe field is compatible only and its default value is NULL. |
| LAST_REFRESH_TYPE | VARCHAR2(8) | NO | The method used in the last refresh.
|
| LAST_REFRESH_DATE | DATE | YES | The date when the materialized view was last refreshed. |
| LAST_REFRESH_END_TIME | DATE | NO | The end time of the last refresh of the materialized view. |
| STALENESS | VARCHAR2(19) | NO | The relationship between the content of the materialized view and that of its master.
NoteThe field is compatible only and its default value is NULL. |
| AFTER_FAST_REFRESH | VARCHAR2(19) | NO | The value that the STALENESS field will take after a fast refresh is applied to the materialized view. It has the same possible values as the STALENESS field. If the value is NA, the STALENESS field is set to this value when fast refreshes are not applicable to the materialized view.
NoteThe field is compatible only and its default value is NULL. |
| UNKNOWN_PREBUILT | VARCHAR2(1) | NO | Specifies whether the materialized view is prebuilt.
NoteThe field is compatible only and its default value is N. |
| UNKNOWN_PLSQL_FUNC | VARCHAR2(1) | NO | Specifies whether the materialized view contains a PL/SQL function.
NoteThe field is compatible only and its default value is N. |
| UNKNOWN_EXTERNAL_TABLE | VARCHAR2(1) | NO | Specifies whether the materialized view contains an external table.
NoteThe field is compatible only and its default value is N. |
| UNKNOWN_CONSIDER_FRESH | VARCHAR2(1) | NO | Specifies whether the materialized view is considered the latest.
NoteThe field is compatible only and its default value is N. |
| UNKNOWN_IMPORT | VARCHAR2(1) | NO | Specifies whether the materialized view is imported.
NoteThe field is compatible only and its default value is N. |
| UNKNOWN_TRUSTED_FD | VARCHAR2(1) | NO | Specifies whether the materialized view uses trusted constraints for refreshes.
NoteThe field is compatible only and its default value is N. |
| COMPILE_STATE | VARCHAR2(19) | NO | The validity of the materialized view relative to the objects on which it depends.
NoteThe field is compatible only and its default value is NULL. |
| USE_NO_INDEX | VARCHAR2(1) | NO | Specifies whether the materialized view was created using the USING NO INDEX clause (Y) or the default index (N). The USING NO INDEX clause prohibits the creation of default indexes.
NoteThe field is compatible only and its default value is Y. |
| STALE_SINCE | DATE | NO | The start time when the materialized view became out-of-date.
NoteThe field is compatible only and its default value is NULL. |
| NUM_PCT_TABLES | NUMBER | NO | The number of PCT base tables.
NoteThe field is compatible only and its default value is NULL. |
| NUM_FRESH_PCT_REGIONS | NUMBER | NO | The number of latest PCT partition or subpartition regions.
NoteThe field is compatible only and its default value is NULL. |
| NUM_STALE_PCT_REGIONS | NUMBER | NO | The number of out-of-date PCT partition or subpartition regions.
NoteThe field is compatible only and its default value is NULL. |
| SEGMENT_CREATED | VARCHAR2(3) | NO | Specifies whether the materialized view was created using the SEGMENT CREATION DEFERRED clause.
NoteThe field is compatible only and its default value is NO. |
| EVALUATION_EDITION | VARCHAR2(128) | NO | The version name of the object referenced in the expression column.
NoteThe field is compatible only and its default value is NULL. |
| UNUSABLE_BEFORE | VARCHAR2(128) | NO | The name of the oldest version where the storage results of the materialized view subquery can be used for query rewriting. The storage results of the materialized view in versions earlier than the specified version are considered unavailable for query rewriting. If such a version is not specified, the value of this field is NULL.
NoteThe field is compatible only and its default value is NULL. |
| UNUSABLE_BEGINNING | VARCHAR2(128) | NO | The name of the oldest version where the storage results of the materialized view subquery cannot be used for query rewriting. In this version and any descendant versions, the data is unavailable for query rewriting. If such a version is not specified, the value of this field is NULL.
NoteThe field is compatible only and its default value is NULL. |
| DEFAULT_COLLATION | VARCHAR2(100) | NO | The default sorting rule of the materialized view.
NoteThe field is compatible only and its default value is NULL. |
| ON_QUERY_COMPUTATION | VARCHAR2(1) | NO | Specifies whether the materialized view is a real-time materialized view.
|
Examples
obclient [SYS]> SELECT * FROM SYS.DBA_MVIEWS WHERE ROWNUM < = 1;
The query result is as follows:
+---------+--------------+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+------------+---------------------+-------------+-----------------+--------------------+--------------+----------------+------------+------------------+-------------------+-------------------+-----------------------+-----------+--------------------+------------------+--------------------+------------------------+------------------------+----------------+--------------------+---------------+--------------+-------------+----------------+-----------------------+-----------------------+-----------------+--------------------+-----------------+--------------------+-------------------+----------------------+
| OWNER | MVIEW_NAME | CONTAINER_NAME | QUERY | QUERY_LEN | UPDATABLE | UPDATE_LOG | MASTER_ROLLBACK_SEG | MASTER_LINK | REWRITE_ENABLED | REWRITE_CAPABILITY | REFRESH_MODE | REFRESH_METHOD | BUILD_MODE | FAST_REFRESHABLE | LAST_REFRESH_TYPE | LAST_REFRESH_DATE | LAST_REFRESH_END_TIME | STALENESS | AFTER_FAST_REFRESH | UNKNOWN_PREBUILT | UNKNOWN_PLSQL_FUNC | UNKNOWN_EXTERNAL_TABLE | UNKNOWN_CONSIDER_FRESH | UNKNOWN_IMPORT | UNKNOWN_TRUSTED_FD | COMPILE_STATE | USE_NO_INDEX | STALE_SINCE | NUM_PCT_TABLES | NUM_FRESH_PCT_REGIONS | NUM_STALE_PCT_REGIONS | SEGMENT_CREATED | EVALUATION_EDITION | UNUSABLE_BEFORE | UNUSABLE_BEGINNING | DEFAULT_COLLATION | ON_QUERY_COMPUTATION |
+---------+--------------+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+------------+---------------------+-------------+-----------------+--------------------+--------------+----------------+------------+------------------+-------------------+-------------------+-----------------------+-----------+--------------------+------------------+--------------------+------------------------+------------------------+----------------+--------------------+---------------+--------------+-------------+----------------+-----------------------+-----------------------+-----------------+--------------------+-----------------+--------------------+-------------------+----------------------+
| USER001 | MV_TEST_TBL1 | __mv_container_500154 | select "USER001"."TEST_TBL1"."COL1" AS "COL1","USER001"."TEST_TBL1"."COL2" AS "COL2","USER001"."TEST_TBL1"."COL3" AS "COL3" from "USER001"."TEST_TBL1" where ("USER001"."TEST_TBL1"."COL3" >= 30) | 193 | N | NULL | NULL | NULL | N | NULL | DEMAND | COMPLETE | IMMEDIATE | NULL | COMPLETE | 19-JAN-24 | 19-JAN-24 | NULL | NULL | N | N | N | N | N | N | NULL | Y | NULL | NULL | NULL | NULL | NO | NULL | NULL | NULL | NULL | N |
+---------+--------------+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+------------+---------------------+-------------+-----------------+--------------------+--------------+----------------+------------+------------------+-------------------+-------------------+-----------------------+-----------+--------------------+------------------+--------------------+------------------------+------------------------+----------------+--------------------+---------------+--------------+-------------+----------------+-----------------------+-----------------------+-----------------+--------------------+-----------------+--------------------+-------------------+----------------------+
1 row in set (0.019 sec)