通过测试来学习DBA_TAB_MODIFICATIONS视图的作用
南城ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为创新互联建站的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：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描述的是收集完统计信息之后的数据库中所有表的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的相关操作。