dba_tab_modifications Tips Oracle Tips by Burleson Consulting

Oracle auditing is time-consuming and expensive, but Oracle does have a view that allows you to see a cumulative number of all of the DML (inserts updates and deletes) for a specific table (cumulative since the last re-analyze date/time):

SQL> desc DBA_TAB_MODIFICATIONS;

Name              Type
--------------------------------
TABLE_OWNER       VARCHAR2(30)
TABLE_NAME        VARCHAR2(30)
PARTITION_NAME    VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
INSERTS           NUMBER
UPDATES           NUMBER
DELETES           NUMBER
TIMESTAMP         DATE
TRUNCATED         VARCHAR2(3)

In addition to auditing, these number are used for the optimizer.  Every time a table is re-analyzed with DBMS_STATS (using the "gather stale" or "gather auto" options), the counters are re-set.  In Oracle 9i, the manual command to enable dba_tab_modifications was "alter table xxx monitoring" to turn-on monitoring for a table, but this is fully-automated in Oracle 10g and beyond.

The central idea behind dba_tab_modifications is to re-analyze a table only after a statistically significant number of table rows have changed and Oracle compares these counts of DML to the num_rows column of dba_tables in order to determine the right time to automatically re-analyze a table. 

Oracle compares the number of modifications with the num_rows column in the dba_tables (based on on the sys.tab$ fixed view) and the modifications are kept in sys.mon_mos$ table.

There is also the corresponding user_tab_modifications and all_tab_modifications.

Also see MOSC Note 762738.1 titled " Why does DBA_TAB_MODIFICATIONS sometimes have no values?"