DBA_TAB_MODIFICATIONS view contains tables that were modified since last time statistics was gathered on the table. It contains the number of inserts, deletes and updates have occurred on the table. In 10g this is turned on automatically when the statistics_level is typical/all.

By calling DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO one can flush monitoring information from memory into dictionary. When DBMS_STATS.GATHER_*_STATS is called it calls DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO as one of the step to flush modification information.

In the example below it shows when dbms_stats.gather_schema_stats is called it flushes modification information in the dictionary.

SQL> select timestamp from dba_tab_modifications where table_owner = ‘SCOTT’;

TABLE_NAME TIMESTAMP
—————————— ——————–
TEST1 09-FEB-2010 22:17:46
TABLE2 09-FEB-2010 22:19:06

SQL> create table scott.table3( x number );

SQL> insert into scott.table3 values ( 10 );

1 row created.

— insert few rows in table3
SQL> /

..

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_schema_stats(‘SYS’);

PL/SQL procedure successfully completed.

SQL> select table_name, timestamp from dba_tab_modifications where table_owner = ‘SCOTT’;
TABLE_NAME TIMESTAMP
—————————— ——————–
TABLE3 09-FEB-2010 22:23:10
TEST1 09-FEB-2010 22:17:46
TABLE2 09-FEB-2010 22:19:06

Information on DBA_TAB_MODIFICATIONS
SQL> desc dba_tab_modifications;
Name Null? Type
—————————————– ——– —————————-
TABLE_OWNER VARCHAR2(30) — table owner
TABLE_NAME VARCHAR2(30) — table name
PARTITION_NAME VARCHAR2(30) — partition name
SUBPARTITION_NAME VARCHAR2(30) — sub partition name
INSERTS NUMBER — number of inserts in the table
UPDATES NUMBER — number of updates in the table
DELETES NUMBER — number of deletes in table
TIMESTAMP DATE — timestamp when the table was
TRUNCATED VARCHAR2(3) — if table was truncated
DROP_SEGMENTS NUMBER — if segments were dropped