DBA_TAB_MODIFICATIONS 視圖學習

通過測試來學習DBA_TAB_MODIFICATIONS視圖的作用

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描述的是收集完統計信息之后的數據庫中所有表的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)  ##截斷
 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> 

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

測試二：

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> 

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