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官方文件資料供參考

STATISTICS_LEVEL

STATISTICS_LEVEL specifies 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 of TYPICAL ensures 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 the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

Setting the STATISTICS_LEVEL parameter to BASIC disables 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_MODEL and V$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

When the STATISTICS_LEVEL parameter is modified by ALTER SYSTEM, all advisories or statistics are dynamically turned on or off, depending on the new value of STATISTICS_LEVEL. When modified by ALTER 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

The V$STATISTICS_LEVEL view displays information about the status of the statistics or advisories controlled by the STATISTICS_LEVEL parameter. See .

USER_TAB_MODIFICATIONS

USER_TAB_MODIFICATIONS describes 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 .

8.小結
USER_TAB_MODIFICATIONS檢視在Oracle的不同版本中也在不斷的做著細微的調整和最佳化，Oracle的細節之處無處不在。
從此我們又多了一條瞭解資料庫執行狀況的手段。

Good luck.

secooler
09.12.01

-- The End --