Monitoring DML ( Insert/Update/Delete) Operations on a Oracle Table

When you are assigned to a task where you have to monitor a particular bulk DML Transaction to see how many rows were updated/deleted/inserted on a particular table, Follow the steps below for successfully completing this task.
( The database version used is 10.2.0.1)

1. Select the count of present DML operations values from DBA_TAB_MODIFICATIONS


SQL> select * from dba_tab_modifications where table_owner='XXX' and table_name='YYYY';

For Example, you are asked to monitor a bulk insert program to find out at a particular time how many rows were inserted.
In this example we used XXDATA table

Take the count of the present values
SQL> select * from dba_tab_modifications where table_name='XXDATA';

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME SUBPARTITION_NAME INSERTS
------------------------------ ------------------------------ ----------
UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
---------- ---------- --------- --- -------------
DEMO XXDATA
272645
0 0 10-JUL-08 NO 0

So now the initial inserts value is 272645

2. Now start your bulk insert

3. Now monitor the Inserts using the following process

Use the dbms.stats_flush_database_monitoring_info to flush the statistics from memory into DBA_TAB_MODIFICATIONS table as shown below

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

Now take the count of the number of inserts by querying the dbs_tab_modifications

SQL> select * from dba_tab_modifications where table_name='XXDATA';

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME SUBPARTITION_NAME INSERTS
------------------------------ ------------------------------ ----------
UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
---------- ---------- --------- --- -------------
DEMO XXDATA
1059999
0 0 10-JUL-08 NO 0

So the Total inserts 787354 (1059999-272645)