通過測試來學(xué)習(xí)DBA_TAB_MODIFICATIONS視圖的作用

南城ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場景，ssl證書未來市場廣闊！成為創(chuàng)新互聯(lián)建站的ssl證書銷售渠道，可以享受市場價格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：18980820575（備注：SSL證書合作）期待與您的合作！

DBA_TAB_MODIFICATIONS describes modifications to all tables in the database 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".

DBA_TAB_MODIFICATIONS描述的是收集完統(tǒng)計(jì)信息之后的數(shù)據(jù)庫中所有表的DML操作。

Note:
This view is populated only for tables with the MONITORING attribute. 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 the FLUSH_DATABASE_MONITORING_INFO procedure in the DBMS_STATS PL/SQL package to populate this view with the latest information. The ANALYZE_ANY system privilege is required to run this procedure.

SQL> desc dba_tab_modifications;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_OWNER                                        VARCHAR2(128)
 TABLE_NAME                                         VARCHAR2(128)
 PARTITION_NAME                                     VARCHAR2(128)
 SUBPARTITION_NAME                                  VARCHAR2(128)
 INSERTS                                            NUMBER    ##插入
 UPDATES                                            NUMBER  ##更新
 DELETES                                            NUMBER  ##刪除
 TIMESTAMP                                          DATE
 TRUNCATED                                          VARCHAR2(3)  ##截?cái)? DROP_SEGMENTS                                      NUMBER

SQL> 

SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
         0

SQL> create table t1 as select *  from dba_objects;

Table created.

SQL> select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1';

no rows selected

SQL> update t1 set object_id=1 where object_id=30;

1 row updated.

SQL> select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1';

no rows selected

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1';

TABLE_OWNER
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
   INSERTS    UPDATES    DELETES TIMESTAMP               TRU DROP_SEGMENTS
---------- ---------- ---------- ----------------------- --- -------------
SYS
T1
         0          1          0 19-FEB-2018 06:59:33    NO              0

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1';

TABLE_OWNER
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
   INSERTS    UPDATES    DELETES TIMESTAMP               TRU DROP_SEGMENTS
---------- ---------- ---------- ----------------------- --- -------------
SYS
T1
         0          1          0 19-FEB-2018 06:59:33    NO              0

SQL> exec dbms_stats.gather_table_stats('SYS','T1');

PL/SQL procedure successfully completed.

SQL> select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1';

no rows selected

SQL> 

小結(jié)：
1、dml操作不提交，也會記錄到視圖中。
2、考慮到性能問題，我們需要手動flush，視圖中才會有記錄。
3、收集完統(tǒng)計(jì)信息，視圖中相關(guān)表記錄置空，與官方文檔描述一樣

測試二：

SQL> alter system set "_dml_monitoring_enabled"=false scope=memory;

System altered.

SQL> delete from t1;

90974 rows deleted.

SQL> select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1';

no rows selected

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from sys.dba_tab_modifications where table_name='T1';

no rows selected

SQL> 

小結(jié)：
關(guān)閉監(jiān)視器以后，不管我們做任何操作，此視圖都不會記錄dml的相關(guān)操作。