Oracle视图user_tab_modifications
user_tab_modifications收集自采集信息以来被改变表的dml操作量数据，一个表只有数据量被改变10%以上才会被定期采集信息，也可以执行过程
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO进行实时采集。
由于user_tab_modifications收集的信息是自第一次收集以来的信息，所以要了解某一段时间对某表的dml操作情况，应该做个快照差。
www.2cto.com
下面给个例子
现在有表t1
SQL> select * from t2;
ID NAME
---------- --------------------------------
1 scott
2 fishcat
查询user_tab_modifications无记录说明没做任何信息收据
SQL> select * from user_tab_modifications where table_name='T2';
no rows selected
使用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO更新下看看结果
SQL> conn / as sysdba
Connected.
SQL> grant ANALYZE any to scott;
Grant succeeded.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select * from user_tab_modifications where table_name='T2';
no rows selected
可以看出依然没有结果
对t2插入一条数据看看
SQL> insert into t2 values(3,'xyc');
1 row created.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select table_name,inserts,updates,deletes,timestamp,drop_segments from user_tab_modifications where table_name='T2';
TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP DROP_SEGMENTS
------------------------------ ---------- ---------- ---------- ------------ -------------
T2 1 0 0 20-FEB-13 0
可以看到一条insert记录inserts为1，下面继续
SQL> update t2 set name='xycxyc' where id=3;
1 row updated.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select table_name,inserts,updates,deletes,timestamp,drop_segments from user_tab_modifications where table_name='T2';
TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP DROP_SEGMENTS
------------------------------ ---------- ---------- ---------- ------------ -------------
T2 1 1 0 20-FEB-13 0
SQL> insert into t2 values(3,'xyc');
1 row created.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select table_name,inserts,updates,deletes,timestamp,drop_segments from user_tab_modifications where table_name='T2';
TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP DROP_SEGMENTS
------------------------------ ---------- ---------- ---------- ------------ -------------
T2 2 1 0 20-FEB-13 0
可以看到inserts和updates的更改
USER_TAB_MODIFICATIONS还记录了表是否被truncate和分区，子分区是否有删除过的记录，参考：
Related Views
DBA_TAB_MODIFICATIONS describes such information for all tables in the database.
USER_TAB_MODIFICATIONS describes such information for tables owned by the current user. This view does not display the TABLE_OWNER column.
Note:
These views are populated only for tables with the MONITORING attribute. They are intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate these views immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DBMS_STATS PL/SQL package to populate these views with the latest information. The ANALYZE_A