DML Activity by Object (including statistics)

This is a helpful script to see DML activity on tables.

By default, if the "Updates" column is over 10%, stats will be gathered on that object.

First, flush DML activity stats to dba_tab_modifications:

dbms_stats.flush_monitoring_info

set linesize 140

set pagesize 50
col table_partition heading 'Table.Partition' format a40
col analyzed heading 'Last Analyzed'
col num_rows heading '# Rows' format 99,999,999,999
col tot_updates heading 'Total DMLs' format 99,999,999,999
col truncd heading 'Truncated?'
col pct_updates heading '%|Updates' format 999.99
col ts heading 'Last DML'
select table_name||decode(partition_name,null,'','.'||partition_name) table_partition,
       to_char(last_analyzed,'MM/DD/YY HH24:MI') analyzed,
       num_rows,
       tot_updates,
       to_char(timestamp,'MM/DD/YY HH24:MI') ts,
       to_number(perc_updates) pct_updates,
       decode(truncated,'NO','','Yes       ') truncd
  from (select a.*,
               nvl(decode(num_rows, 0, '-1', 100 * tot_updates / num_rows), -1) perc_updates
          from (select (select num_rows
                         from dba_tables
                        where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name
                          and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) num_rows,
                       (select last_analyzed
                          from dba_tables
                         where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name
                           and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) last_analyzed,
                       (inserts + updates + deletes) tot_updates,
                       DBA_TAB_MODIFICATIONS.*
                  from sys.DBA_TAB_MODIFICATIONS
               ) a
       ) b
 where perc_updates > 5
   and table_owner = '&SCHEMA'
 order by last_analyzed desc
/
exit
/


SAMPLE OUTPUT:

Table.Partition      Last Analyzed           # Rows      Total DMLs Last DML       Updates Truncated?
-------------------- -------------- --------------- --------------- -------------- ------- ----------
AQ$_QUEUE_TABLES     03/12/13 14:32              11               1 03/12/13 14:36    9.09