First, we need to see if there is an attribute "MONITORING" of the table.
Just query 'all_tables':
SQL> select owner,MONITORING from all_tables where TABLE_NAME=''EVENT_BILLING_PRODUCT_T";
OWNER MON
------------------------------ ---
PIN YES
###MONITORING VARCHAR2(3) Whether the table has the MONITORING attribute set
###一般情况下,在建表时我们会经常使用ORACLE的默认参数。而MONITORING这个参数9i 中默认是关闭的, 10g默认为开启。非默认时使用NOMONITORING参数。
If the value for 'MONITORING' is 'No', we should alter the value:
SQL> alter table EVENT_BILLING_PRODUCT_T monitoring;
Table altered.
Then, query the ALL_TAB_MODIFICATIONS, which describes tables accessible to the
current user that have been modified since the last time statistics
were gathered on the tables.
###其中记录了该表或表的PARTITION的INSERT/UPDATE/DELETE/DROP_SEGMENTS/TRUNCAE的次数。但要注意的一点就是:INSERT/UPDATE/DELETE只能记录一个大概次数。而不是十分精确的次数。以上为ORACLE官方文档提供的 DBA_TAB_MODIFICATIONS字段及其说明。
SQL> select TABLE_NAME,PARTITION_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP from all_TAB_MODIFICATIONS where table_name='EVENT_BILLING_PRODUCT_T';
TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP
------------------------------ -------------------- ---------- ---------- ---------- ---------
EVENT_BILLING_PRODUCT_T 9140 0 0 07-OCT-11
EVENT_BILLING_PRODUCT_T PARTITION_HISTORIC 4042 0 0 07-OCT-11
EVENT_BILLING_PRODUCT_T PARTITION_LAST 5098 0 0 07-OCT-11
SQL> /
TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP
------------------------------ -------------------- ---------- ---------- ---------- ---------
EVENT_BILLING_PRODUCT_T 9140 0 0 07-OCT-11
EVENT_BILLING_PRODUCT_T PARTITION_HISTORIC 4042 0 0 07-OCT-11
EVENT_BILLING_PRODUCT_T PARTITION_LAST 5098 0 0 07-OCT-11
###需要注意的重要一点,出于保护ORACLE数据库性能方面的考虑,在XXX_TAB_MODIFICATIONS表中显示的数据并不是实时的数据,如果需要看到最近发生的最新数据,可以使用DBMS_STAT包来统计最新变化。
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed
更进一步的探讨,可以参考以下的链接:
http://www.oracledatabase12g.com/archives/smon-flush-dml-statistics-mon-mods.html