Oracle Table Monitoring - DML ACTIVITY

Monitoring tracks the approximate number of deletes, updates & inserts operations for the table since the last time statistics were gathered. Information about how many rows are affected is maintained in the SGA, until periodically (about every three hours) SMON incorporates the data into the data dictionary. 

Enabling table monitoring :

create table TEST as select OBJECT_name from all_objects;
table TEST created.

select count(*) from test;

  COUNT(*)
----------
    120118


select table_name,inserts,updates,deletes,truncated,timestamp
   from sys.dba_tab_modifications
  where table_owner='ATOORPU' and table_name='TEST';

  --- no rows selected

  Enable FLUSH_DATABASE
  exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
  anonymous block completed

  ENABLE MONITORING ON TABLE:

  alter table TEST monitoring;

  select table_name,inserts,updates,deletes,truncated,timestamp
   from sys.dba_tab_modifications
  where table_owner='ATOORPU' and table_name='TEST';

  --- no rows selected

  insert into ABCC (select * from ABCC);

  delete from TEST where rownum<1000;

    select count(*) from test;

    COUNT(*)
----------
    119119


select table_name,inserts,updates,deletes,truncated,timestamp
   from sys.dba_tab_modifications
  where table_owner='ATOORPU' and table_name='TEST';

  --- no rows selected

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

  anonymous block completed

  select table_name,inserts,updates,deletes,truncated,timestamp
   from sys.dba_tab_modifications
  where table_owner='ATOORPU' and table_name='TEST';

TABLE_NAME                        INSERTS    UPDATES    DELETES TRUNCATED TIMESTAMP
------------------------------ ---------- ---------- ---------- --------- ---------
TEST                                    0          0        999 NO        18-JUN-1



Note :

Starting with Oracle Database 11g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.