新知识点1： USER_TAB_MODIFICATIONS
key:
select monitoring from user_tables where table_name='TABLE'
alter table t monitoring
exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

转自 http://foxmile.blog.163.com/blog/static/81169805201142605356847/

USER_TAB_MODIFICATIONS视图记录了当前用户中表自最后一次分析之后所有增、删、改等操作信息。通过对其查询可以得到一个简化版的审计功能效果。
因为该视图中记录的信息会因表的分析自动清除，同时Oracle从性能上考虑并没有保证这个视图与表的操作实时保持一致，有一定的滞后，因此其中记录的信息仅可用于参考之目的。

在早期Oracle版本中，若要启用这个功能需要使用“alter table t monitoring;”语句使表具有monitoring属性。到了Oracle 10g时代，该功能已是默认行为，这主要归功于STATISTICS_LEVEL参数，该参数共有三种取值（STATISTICS_LEVEL = { ALL | TYPICAL | BASIC }），当其值是“TYPICAL”或“ALL”时该功能就会生效，因其默认值是“TYPICAL”所以该功能默认被启用。

这里，我通过实验给大家展示一下这个功能，并提出一点注意事项，供大家参考。

1.创建实验表T
sec@ora10g> create table t (x int);

Table created.

2.向表T中插入一条记录，注意这里我们先不提交。
sec@ora10g> insert into t values (6);

1 row created.

3.尝试查询USER_TAB_MODIFICATIONS视图
sec@ora10g> select TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP from user_tab_modifications;

no rows selected

此时是没有结果的，因为我们刚刚完成插入操作，维护到这个视图是需要一段时间的。

4.如何缩短这个等待的过程呢？
执行DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO这个存储过程可以瞬间得到结果。
1）执行存储过程
注意，在普通用户中想要执行这个存储过程，需要被授予“analyze any”的系统权限
sec@ora10g> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
BEGIN DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2148
ORA-06512: at "SYS.DBMS_STATS", line 14135
ORA-06512: at line 1

sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> grant analyze any to sec;

Grant succeeded.

sys@ora10g> conn sec/sec
Connected.
sec@ora10g> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

2）此时已经可以得到查询结果，结果显示T表在“2009-12-01 20:01:06”时被插入了一条记录。
sec@ora10g> select TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP from user_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP
---------- ------- ------- ------- -------------------
T                1       0       0 2009-12-01 20:01:06

5.这里要特别注意一点：在执行完FLUSH_DATABASE_MONITORING_INFO存储过程之后，所有之前的操作都将被提交！
验证如下
1）查看表T中的数据
sec@ora10g> select * from t;

         X
----------
         6

2）尝试回滚（之前没有进行过提交和回滚操作）
sec@ora10g> rollback;

Rollback complete.

3）此时发现，即使显式回滚也无济于事，插入操作已经被提交。
sec@ora10g> select * from t;

         X
----------
         6

4）USER_TAB_MODIFICATIONS视图中的记录也被保留了下来
sec@ora10g> select TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP from user_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP
---------- ------- ------- ------- -------------------
T                1       0       0 2009-12-01 20:01:06

6.再来验证一下表被分析之后的效果
1）分析前
sec@ora10g> select TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP from user_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP
---------- ------- ------- ------- -------------------
T                1       0       0 2009-12-01 20:01:06

2）分析中
sec@ora10g> analyze table t compute statistics;

Table analyzed.

3）分析后
sec@ora10g> select TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP from user_tab_modifications;

no rows selected

实验结论得证。

7.摘录一些10gR2官方文档资料供参考
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams211.htm#REFRN10214
STATISTICS_LEVEL

PropertyDescription
Parameter typeString
SyntaxSTATISTICS_LEVEL = { ALL | TYPICAL | BASIC }
Default valueTYPICAL
ModifiableALTER SESSION,ALTER SYSTEM

STATISTICS_LEVELspecifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.

The default setting ofTYPICALensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.

When theSTATISTICS_LEVELparameter is set toALL, additional statistics are added to the set of statistics collected with theTYPICALsetting. The additional statistics are timed OS statistics and plan execution statistics.

Setting theSTATISTICS_LEVELparameter toBASICdisables the collection of many of the important statistics required by Oracle Database features and functionality, including:

Automatic Workload Repository (AWR) Snapshots
Automatic Database Diagnostic Monitor (ADDM)
All server-generated alerts
Automatic SGA Memory Management
Automatic optimizer statistics collection
Object level statistics
End to End Application Tracing (V$CLIENT_STATS)
Database time distribution statistics (V$SESS_TIME_MODELandV$SYS_TIME_MODEL)
Service level statistics
Buffer cache advisory
MTTR advisory
Shared pool sizing advisory
Segment level statistics
PGA Target advisory
Timed statistics
Monitoring of statistics

Note:
Oracle strongly recommends that you do not disable these important features and functionality.

When theSTATISTICS_LEVELparameter is modified byALTER SYSTEM, all advisories or statistics are dynamically turned on or off, depending on the new value ofSTATISTICS_LEVEL. When modified byALTER SESSION, the following advisories or statistics are turned on or off in the local session only. Their system-wide state is not changed:

Timed statistics
Timed OS statistics
Plan execution statistics

TheV$STATISTICS_LEVELview displays information about the status of the statistics or advisories controlled by theSTATISTICS_LEVELparameter. See"V$STATISTICS_LEVEL".

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4465.htm#sthref2375

USER_TAB_MODIFICATIONS

USER_TAB_MODIFICATIONSdescribes modifications to all tables owned by the current user that have been modified since the last time statistics were gathered on the tables. Its columns are the same as those in"ALL_TAB_MODIFICATIONS".

Note:
This
 view is populated only for tables with theMONITORINGattribute. It is 
intended for statistics collection over a long period of time. For 
performance reasons, the Oracle Database does not populate this view 
immediately when the actual modifications occur. Run 
theFLUSH_DATABASE_MONITORING_INFOprocedure in theDIMS_STATSPL/SQL 
package to populate this view with the latest information. 
TheANALYZE_ANYsystem privilege is required to run this procedure.

8.小结
USER_TAB_MODIFICATIONS视图在Oracle的不同版本中也在不断的做着细微的调整和优化，Oracle的细节之处无处不在。
从此我们又多了一条了解数据库运行状况的手段。

ITPub博客

2011-12-14 USER_TAB_MODIFICATIONS

最新文章

Property	Description
Parameter type	String
Syntax	STATISTICS_LEVEL = { ALL \| TYPICAL \| BASIC }
Default value	TYPICAL
Modifiable	ALTER SESSION,ALTER SYSTEM