Note
This view was introduced in OceanBase Database V4.3.0.
Purpose
The USER_MVIEWS view displays the information about materialized views.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | YES | 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 that defines the materialized view, in bytes. |
| UPDATABLE | VARCHAR2(1) | NO | Indicates whether the materialized view can be updated. Valid values:
NoteThis column is used only for compatibility and its value is |
| UPDATE_LOG | VARCHAR2(128) | NO | The name of the update log file, if the materialized view can be updated.
NoteThis column is used only for compatibility and its value is |
| MASTER_ROLLBACK_SEG | VARCHAR2(128) | NO | The rollback segment of the master site or master materialized view site.
NoteThis column is used only for compatibility and its value is |
| MASTER_LINK | VARCHAR2(128) | NO | The database link of the master site or master materialized view site.
NoteThis column is used only for compatibility and its value is |
| REWRITE_ENABLED | VARCHAR2(1) | NO | Indicates whether rewrite is enabled. Valid values:
NoteThis column is used only for compatibility and its value is |
| REWRITE_CAPABILITY | VARCHAR2(9) | NO | Indicates whether the materialized view meets the rewrite conditions. Valid values:
NoteThis column is used only for compatibility and its value is |
| REFRESH_MODE | VARCHAR2(6) | NO | The mode for refreshing the materialized view. Valid values:
|
| REFRESH_METHOD | VARCHAR2(8) | NO | The default method for refreshing the materialized view, which can be overridden by using an API. Valid values:
|
| BUILD_MODE | VARCHAR2(9) | NO | The mode for populating the materialized view during creation. Valid values:
|
| FAST_REFRESHABLE | VARCHAR2(18) | NO | Indicates whether the materialized view is eligible for incremental (fast) refreshes. Valid values:
NoteThis column is used only for compatibility and its value is |
| LAST_REFRESH_TYPE | VARCHAR2(8) | NO | The method used for the last refresh. Valid values:
|
| LAST_REFRESH_DATE | DATE | YES | The date when the materialized view was last refreshed. |
| LAST_REFRESH_END_TIME | DATE | NO | The time when the last refresh of the materialized view ended. |
| STALENESS | VARCHAR2(19) | NO | The relationship between the content of the materialized view and that of the masters of the materialized view. Valid values:
NoteThis column is used only for compatibility and its value is |
| AFTER_FAST_REFRESH | VARCHAR2(19) | NO | The STALENESS value when the materialized view is fast refreshed, which is the same as the value of the STALENESS column. The value NA indicates that fast refresh is not supported for the materialized view.
NoteThis column is used only for compatibility and its value is |
| UNKNOWN_PREBUILT | VARCHAR2(1) | NO | Indicates whether the materialized view was prebuilt. Valid values:
NoteThis column is used only for compatibility and its value is |
| UNKNOWN_PLSQL_FUNC | VARCHAR2(1) | NO | Indicates whether the materialized view contains PL/SQL functions. Valid values:
NoteThis column is used only for compatibility and its value is |
| UNKNOWN_EXTERNAL_TABLE | VARCHAR2(1) | NO | Indicates whether the materialized view contains external tables. Valid values:
NoteThis column is used only for compatibility and its value is |
| UNKNOWN_CONSIDER_FRESH | VARCHAR2(1) | NO | Indicates whether the materialized view is considered fresh. Valid values:
NoteThis column is used only for compatibility and its value is |
| UNKNOWN_IMPORT | VARCHAR2(1) | NO | Indicates whether the materialized view was imported from another database. Valid values:
NoteThis column is used only for compatibility and its value is |
| UNKNOWN_TRUSTED_FD | VARCHAR2(1) | NO | Indicates whether trusted constraints are used to refresh the materialized view. Valid values:
NoteThis column is used only for compatibility and its value is |
| COMPILE_STATE | VARCHAR2(19) | NO | The validity of the materialized view with respect to the objects on which it depends. Valid values:
NoteThis column is used only for compatibility and its value is |
| USE_NO_INDEX | VARCHAR2(1) | NO | Indicates whether the materialized view was created by using the USING NO INDEX clause (Y) or the default index (N). You cannot create a default index when you use the USING NO INDEX clause.
NoteThis column is used only for compatibility and its value is |
| STALE_SINCE | DATE | NO | The time since when the materialized view became stale.
NoteThis column is used only for compatibility and its value is |
| NUM_PCT_TABLES | NUMBER | NO | The number of PCT base tables.
NoteThis column is used only for compatibility and its value is |
| NUM_FRESH_PCT_REGIONS | NUMBER | NO | The number of fresh PCT partition regions.
NoteThis column is used only for compatibility and its value is |
| NUM_STALE_PCT_REGIONS | NUMBER | NO | The number of stale PCT partition regions.
NoteThis column is used only for compatibility and its value is |
| SEGMENT_CREATED | VARCHAR2(3) | NO | Indicates whether the materialized view was created by using the SEGMENT CREATION DEFERRED clause. Valid values:
NoteThis column is used only for compatibility and its value is |
| EVALUATION_EDITION | VARCHAR2(128) | NO | The edition in which editioned objects referenced in an expression column are resolved.
NoteThis column is used only for compatibility and its value is |
| UNUSABLE_BEFORE | VARCHAR2(128) | NO | The earliest edition in which the stored results of subqueries on the materialized view can be used for query rewrites. In an edition earlier than the specified edition, the stored query results of the materialized view are considered as unavailable. If no such edition is specified, the value is NULL.
NoteThis column is used only for compatibility and its value is |
| UNUSABLE_BEGINNING | VARCHAR2(128) | NO | The earliest edition in which the stored results of subqueries on the materialized view cannot be used for query rewrites. In the specified edition and later editions, the data cannot be used for query rewrites. If no such edition is specified, the value is NULL.
NoteThis column is used only for compatibility and its value is |
| DEFAULT_COLLATION | VARCHAR2(100) | NO | The default collation for the materialized view.
NoteThis column is used only for compatibility and its value is |
| ON_QUERY_COMPUTATION | VARCHAR2(1) | NO | Indicates whether the materialized view is a real-time view. Valid values:
NoteThis column is used only for compatibility and its value is |
Sample query
obclient [SYS]> SELECT * FROM SYS.USER_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 |
+-------+------------+-----------------------+-------------------------------------------------------------------------------------------------------------------+-----------+-----------+------------+---------------------+-------------+-----------------+--------------------+--------------+----------------+------------+------------------+-------------------+-------------------+-----------------------+-----------+--------------------+------------------+--------------------+------------------------+------------------------+----------------+--------------------+---------------+--------------+-------------+----------------+-----------------------+-----------------------+-----------------+--------------------+-----------------+--------------------+-------------------+----------------------+
| SYS | MV_TBL1 | __mv_container_500179 | select "SYS"."TBL1"."ID" AS "ID","SYS"."TBL1"."NAME" AS "NAME" from "SYS"."TBL1" where ("SYS"."TBL1"."AGE" >= 20) | 113 | N | NULL | NULL | NULL | N | NULL | DEMAND | FORCE | IMMEDIATE | NULL | COMPLETE | 20-JAN-24 | 20-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.046 sec)
