Note
This view is introduced since OceanBase Database V4.3.0.
Purpose
The DBA_MVREF_CHANGE_STATS view displays the refresh statistics of materialized views. The statistics show the data changes on the base tables associated with each materialized view refresh, as well as the information required to load the changed data.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| TBL_OWNER | VARCHAR2(128) | YES | The owner of the base table. |
| TBL_NAME | VARCHAR2(128) | YES | The name of the base table. |
| MV_OWNER | VARCHAR2(128) | YES | The owner of the materialized view. |
| MV_NAME | VARCHAR2(128) | YES | The name of the materialized view. |
| REFRESH_ID | NUMBER | NO | The ID of the refresh. |
| NUM_ROWS_INS | NUMBER | YES | The number of rows inserted. |
| NUM_ROWS_UPD | NUMBER | YES | The number of rows updated. |
| NUM_ROWS_DEL | NUMBER | YES | The number of rows deleted. |
| NUM_ROWS_DL_INS | NUMBER | NO | The number of rows inserted by using the Direct-load INSERT statement.
NoteThis column is used only for compatibility and is |
| PMOPS_OCCURRED | CHAR(1) | NO | Indicates whether a partition maintenance operation (PMOP) has been performed. Valid values:
NoteThis column is used only for compatibility and is |
| PMOP_DETAILS | VARCHAR2(4000) | NO | The detailed information about the PMOPs, in the following formats:
NoteThis column is used only for compatibility and is |
| NUM_ROWS | NUMBER | YES | The number of rows in the base table when the refresh starts. |
Sample query
obclient [SYS]> SELECT * FROM SYS.DBA_MVREF_CHANGE_STATS;
The query result is as follows:
+-----------+-----------+----------+---------------+------------+--------------+--------------+--------------+-----------------+----------------+--------------+----------+
| TBL_OWNER | TBL_NAME | MV_OWNER | MV_NAME | REFRESH_ID | NUM_ROWS_INS | NUM_ROWS_UPD | NUM_ROWS_DEL | NUM_ROWS_DL_INS | PMOPS_OCCURRED | PMOP_DETAILS | NUM_ROWS |
+-----------+-----------+----------+---------------+------------+--------------+--------------+--------------+-----------------+----------------+--------------+----------+
| SYS | TEST_TBL1 | SYS | MV1_TEST_TBL1 | 825195 | 3 | 0 | 0 | 0 | N | NULL | 0 |
+-----------+-----------+----------+---------------+------------+--------------+--------------+--------------+-----------------+----------------+--------------+----------+
1 row in set (0.028 sec)