How to find the number of inserts, updates, and deletes ( DML ) on an Oracle table

The quick answer -> DBA_TAB_MODIFICATIONS  (Unless you actually want SQL%ROWCOUNT)

I seem to find myself searching for the name of this view every 6 months or so for one for one reason or another. I always seems to have trouble finding it to when I do searches:

  • How to find the number of inserts, updates, and deletes on an Oracle table?
  • How can I find if rows are being inserted, updated, or deleted on  table?
  • Table with count of inserts, updates, and deletes.
  • Amount of DML performed on a table
  • How many DML statements have been done over a period of time?
  • etc…

Usually I come up with the usage of SQL%ROWCOUNT, etc. At least now I’ll find it easily and I’m hoping you found this easily too.

Oracle does track how many inserts, updates, and deletes are performed on a table in the DBA_TAB_MODIFICATIONS view. It is not kept real-time so if you need to examine it over a small time period then you have to flush the stats so you can see (easy).

Here is an example of how to do that. In this case, I had a table with a delete process that wasn’t keeping up. The row count kept getting higher, but I wanted to be sure that rows were in fact being deleted. With DBA_TAB_MODIFICATIONS I had an easy way to find out for sure (as opposed to a long query to find rows.)

SQL> execute dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

SQL> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications  where table_name = 'RDS_LOG';
TABLE_OWNER                    TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ------------------------------ ---------- ---------- ----------
MYUSER                         RDS_LOG                           4658668          0    1119300

SQL> exec dbms_lock.sleep(60);
PL/SQL procedure successfully completed.

SQL> execute dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

SQL> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications  where table_name = 'RDS_LOG';
TABLE_OWNER                    TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ------------------------------ ---------- ---------- ----------
MYUSER                         RDS_LOG                           4660111          0    1119531

4 responses to “How to find the number of inserts, updates, and deletes ( DML ) on an Oracle table

  1. Pingback: How to find the number of inserts, updates, and deletes on an Oracle table | Jed's

  2. Pingback: Find tables with high inserts/updates and rebuilding indexes | Jed's

  3. Hugh Engels

    hi, i wanna know how many updates and inserts have been performed in the last month? how do i do that?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s