Total number of DML and DDL operations performed in a table in oracle

Question

I would like to know the total number of operations (DDL and DDL) performed on a table in oracle dabatase since it was created.

Is there a query for it?

Answer 1

If your database has been running in ARCHIVELOG mode and supplemental logging is enabled, you can historically mine the archive logs using Log Miner.

There's a nice demo on this page.

May I ask why you want the total number of operations? The numbers by themselves are meaningless.

Answer 2

No, there is no simple query for this, if you did not setup auditing for this.

You could use auditing to track ddl that changes tables. You could create a procedure that tracks the # of rows affected by dml operations by reading dba_tab_modifications, before the table is analyzed, and store that in an own table to keep a historical view on it. Not for the # of dml. If you have ASH available you could also find out the # of dml on a table and record those statistics in own tables. ASH tends to grow a lot so there is a purging policy on it.

So, the simple answer is no but as is the case with many things in Oracle database, with a little creative thinking, it can be made available.

Answer 3

Based on your comments, it sounds like you are really interested in knowing the growth of the object over time. You could create a table to store this data and as frequently as you want to see changes, insert into the table the results of the this query:

SELECT owner, segment_name, sum(blocks) BlockCount, sysdate GatherDate
FROM dba_extents WHERE segment_name='**Your Table Name Here***' GROUP BY owner, segment_name;

You could then create queries to show differences between different time periods or even graph the results.

Answer 4

SQL> execute dbms_stats.flush_database_monitoring_info;

SQL> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications ;