In this Document

APPLIES TO:

SYMPTOMS

From 10gR2 onwards the views DBA_TAB_MODIFICATIONS, USER_TAB_MODIFICATIONS and ALL_TAB_MODIFCATIONS are only refreshed once a day, whereas previous versions are refreshed approximately every 3 hours.

CAUSE

This is explained in an internal update to Bug 13567298 'USER_TAB_MODIFICATIONS' TABLE IS NOT BEING REFRESHED as follows:

A change was made in 10gR2in the way the monitoring code functions. The flushing of DML information to mon_mods$ is done every 15 minutes. However, only 1 minute is spent during the flush and therefore it may not flush all the information. The DML information is maintained in the SGA at segment level only. For example, for a partitioned table, the dml information is maintained only at partition level. This is the information that is flushed to mon_mods$.

Once a day, as part of a background job, this information is aggregated to table level and transferred to mon_mods_all$.
The views *_TAB_MODIFICATIONS are based on mon_mods_all$ and therefore will not be up to date until this job runs.

This aggregation and transferring will also occur during  flush_database_monitoring_info. As this procedure is called during gathering of statistic, the information will be correct when gathering statistics.

SOLUTION

In a scenario when the procedure dbms_stats.gather_schema_stats('list_stale') clause needs to be used, it is documented that before running this procedure
the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO should be run manually to get the correct information.

When querying *_TAB_MODIFICATIONS view you should ensure that you run DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO before doing so in order to obtain accurate results.

REFERENCES