Note
This view is available starting with V4.3.0.
Purpose
The USER_MVREF_CHANGE_STATS view displays statistics related to materialized view refreshes. These statistics indicate the number of data changes in the base tables associated with each materialized view refresh and the information required to load these changes.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| TBL_OWNER | VARCHAR2(128) | YES | The name of the base table owner. |
| TBL_NAME | VARCHAR2(128) | YES | The name of the base table. |
| MV_OWNER | VARCHAR2(128) | YES | The name of the materialized view owner. |
| MV_NAME | VARCHAR2(128) | YES | The name of the materialized view. |
| REFRESH_ID | NUMBER | NO | The refresh ID. |
| 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 directly loaded.
NoteThis column is compatible only and defaults to 0. |
| PMOPS_OCCURRED | CHAR(1) | NO | Indicates whether a partition maintenance operation (PMOP) occurred. Valid values:
NoteThis column is compatible only and defaults to N. |
| PMOP_DETAILS | VARCHAR2(4000) | NO | The details of the PMOP, in the following format:
NoteThis column is compatible only and defaults to NULL. |
| NUM_ROWS | NUMBER | YES | The number of rows in the base table at the start of the refresh. |
Sample query
obclient [SYS]> SELECT * FROM SYS.USER_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.022 sec)