Note
This view is available starting with V4.3.0.
Purpose
The DBA_MVREF_CHANGE_STATS view displays statistics related to materialized view refreshes. These statistics show how many data changes occurred in the associated base tables during 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 inserted by direct load.
NoteThis column is compatible only, and the default value is 0. |
| PMOPS_OCCURRED | CHAR(1) | NO | Indicates whether a partition maintenance operation (PMOP) occurred.
NoteThis column is compatible only, and the default value is N. |
| PMOP_DETAILS | VARCHAR2(4000) | NO | The details of the PMOP in the following format:
NoteThis column is compatible only, and the default value is NULL. |
| NUM_ROWS | NUMBER | YES | The number of rows in the base table at the start of the refresh operation. |
Sample query
Query statistics related to materialized view refreshes.
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)