Initially, I thought to use DBA_TAB_MODIFICATIONS, but it requires to run dbms_stats.flush_database_monitoring_info and gather table statistics of rest the counter, and time co-related to mview refresh.
For performance reasons, the Oracle Database does not populate this view immediately when the actual modifications occur. Run the
FLUSH_DATABASE_MONITORING_INFO
procedure in the DIMS_STATS
PL/SQL package to populate this view with the latest information. Confirmed from https://docs.oracle.com/cd/E26401_01/doc.122/e48791/T578797T589127.htm#T578863, that count on MLOG$_
MLOG$ Table
An MLOG$ table is the Snapshot Log table created against a base table. A row is inserted into the table each time a change (insert/update/delete) is made to the base table.
When a snapshot is refreshed in fast (incremental) mode, it reads the rows in the MLOG$ table and uses this information to get the updated information from the base table into the snapshot for further processing.
Standard RDBMS functionality manages rows inserted into the MLOG tables. When a snapshot is refreshed in either fast or complete mode, then the RDBMS will delete rows from the MLOG table. However, if multiple snapshots reference the same MLOG, then all the snapshots must be refreshed before the RDBMS can remove any rows from the MLOG table.
The RDBMS creates snapshot logs on the base table and automatically names them using MLOG$_[20 characters of the table name]. For example:
MRP_FORECAST_DATES (18 char) creates MLOG$_MRP_FORECAST_DATES BOM_DEPARTMENT_RESOURCES (24 char) creates MLOG$_BOM_DEPARTMENT_RESOU
In case of a tie, the RDBMS adds a number to the MLOG name. For example:
WSM_COPY_OP_RESOURCES creates MLOG$_WSM_COPY_OP_RESOURCE WSM_COPY_OP_RESOURCE_INSTANCES creates MLOG$_WSM_COPY_OP_RESOURCE1
SQL> select count(*) from mlog$_bonus;
COUNT(*)
----------0
from http://www.dba-oracle.com/t_mlog_dollar_tables.htm is a real case.
Moreover, questions to programer:
What is the purpose of getting this number?
What actions should be taken if the amount exceeds certain number? What number should be set as threshold?
Thanks for the challenge.