DBA_TAB_MODIFICATIONS showing no rows? Here's why

This comes up more often than you'd think. You've done everything right — STATISTICS_LEVEL is set to TYPICAL, you've gathered schema stats with DBMS_STATS.GATHER_SCHEMA_STATS, and every table in the schema shows MONITORING = YES in DBA_TABLES. But when you query DBA_TAB_MODIFICATIONS, you get nothing back.

The reason is simpler than most people expect. Oracle doesn't log every single DML change into that view. It only registers a table once the volume of changes clears a meaningful bar — somewhere around 10% of the table's data. One update, ten inserts, a handful of deletes — none of that is enough. The view is designed to help Oracle decide when statistics have gone stale, not to be a full audit trail.

There's also a timing issue. Even when changes do cross the threshold, Oracle keeps that information in the SGA first and flushes it to the dictionary periodically — every 3 hours by default. So even if you've crossed the threshold, you may not see it in the view yet.

Here's a walkthrough that shows exactly what happens.

Start with a simple table and insert 100 rows:

CREATE TABLE test (num VARCHAR2(32));

BEGIN
  FOR i IN 1..100 LOOP
    INSERT INTO sys.test VALUES (i);
  END LOOP;
  COMMIT;
END;
/

Gather stats, then check both views:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TEST');

SELECT OWNER, TABLE_NAME, STALE_STATS, NUM_ROWS
FROM   DBA_TAB_STATISTICS
WHERE  OWNER = 'SYS' AND TABLE_NAME = 'TEST';

OWNER   TABLE_NAME   STALE_STATS   NUM_ROWS
-----   ----------   -----------   --------
SYS     TEST         NO            100

SELECT * FROM DBA_TAB_MODIFICATIONS
WHERE TABLE_OWNER = 'SYS' AND TABLE_NAME = 'TEST';

no rows selected

Expected. Stats are fresh, nothing has changed since the gather. Now flush the in-memory monitoring data to rule out the timing issue:

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SELECT * FROM DBA_TAB_MODIFICATIONS
WHERE TABLE_OWNER = 'SYS' AND TABLE_NAME = 'TEST';

no rows selected

Still nothing — and that's correct, because no DML has happened since the stats were gathered. Now insert 1,000 more rows, bringing the total to 1,100:

BEGIN
  FOR i IN 1..1000 LOOP
    INSERT INTO sys.test VALUES (i);
  END LOOP;
  COMMIT;
END;
/

Before flushing, the view still shows nothing. After:

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SELECT OWNER, TABLE_NAME, STALE_STATS, NUM_ROWS
FROM   DBA_TAB_STATISTICS
WHERE  OWNER = 'SYS' AND TABLE_NAME = 'TEST';

OWNER   TABLE_NAME   STALE_STATS   NUM_ROWS
-----   ----------   -----------   --------
SYS     TEST         YES           100

SELECT * FROM DBA_TAB_MODIFICATIONS
WHERE TABLE_OWNER = 'SYS' AND TABLE_NAME = 'TEST';

TABLE_OWNER   TABLE_NAME   INSERTS   UPDATES   DELETES   TIMESTAMP
-----------   ----------   -------   -------   -------   ---------
SYS           TEST         1000      0         0         19-JUN-12

There it is. The 1,000 inserts pushed past the ~10% threshold relative to the last recorded row count of 100, so Oracle flagged the stats as stale and wrote the entry to DBA_TAB_MODIFICATIONS.

So if you're staring at an empty DBA_TAB_MODIFICATIONS and wondering what's wrong — chances are either not enough data has changed, or it just hasn't been flushed yet. Run DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO first, and if you still see nothing, that's your answer: the change volume isn't there yet.