1 官网说明

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".


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.

地址：http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_2107.htm#REFRN20280

该视图作用：

DBA_TAB_MODIFICATIONS记录了自上次收集表统计信息以来对数据库中所有表的修改操作，包括增删改查，通过查看视图的结构可以发现。

SQL> desc DBA_TAB_MODIFICATIONS

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 TABLE_OWNER                                        VARCHAR2(30)

 TABLE_NAME                                         VARCHAR2(30)

 PARTITION_NAME                                     VARCHAR2(30)

 SUBPARTITION_NAME                                  VARCHAR2(30)

 INSERTS                                            NUMBER     --插入操作次数

 UPDATES                                            NUMBER    --更新操作次数

 DELETES                                            NUMBER	--删除操作次数

 TIMESTAMP                                          DATE

 TRUNCATED                                          VARCHAR2(3)  --自从上次分析之后是否被truncate过。

 DROP_SEGMENTS                                      NUMBER

可以调用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO手动更新该视图里的内容，当然数据库会自动调用该过程去更新。

2 测试

2.1 环境

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

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.2 创建测试表

SQL> create table tt as select * from dba_tables;

 

Table created.

2.3 查看表是否开启monitor

SQL> select MONITORING from dba_tables t where t.table_name='TT';

 

MON

---

YES

没有问题。

2.4 查看视图是否该表的记录

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

 

no rows selected

可以看到没有该表的信息

2.5 手动更新

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='TT';

 

no rows selected

2.6 插入数据

SQL> insert into tt select * from dba_tables where rownum <=10;

 

10 rows created.

--不提交

2.7 再次手动更新

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO



PL/SQL procedure successfully completed.

--可以看到 INSERTS列为10，和之前插入语句相符。插入了十条数据。

SQL> select table_owner,table_name,inserts,UPDATES,deletes,timestamp,truncated,drop_segments from

sys.dba_tab_modifications where table_name ='TT';

TABLE_OWNER                    TABLE_NAME                        INSERTS    UPDATES    DELETES TIMESTAMP    TRU DROP_SEGMENTS

------------------------------ ------------------------------ ---------- ---------- ---------- ------------ --- -------------

SYS                            TT                                     10          0          0 05-MAY-17    NO

注意：

1. 即使没有提交，也会有记录

2. 刚创建的表时，插入的数据是没有被记录的。

2.8 收集表统计信息

SQL> exec dbms_stats.gather_table_stats(OWNNAME =>'SYS',TABNAME =>'TT');

 

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 = 'TT';

 

no rows selected

2.9 对该表做DML操作

--更新两行数据

SQL> update tt set table_name ='ttt' where table_name ='TYPE_MISC$';

 

2 rows updated.

--删除一行数据

SQL> delete tt where rownum =1;

 

1 row deleted.

--手动更新

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 ='TT';

 

TABLE_OWNER                    TABLE_NAME                        INSERTS    UPDATES    DELETES TIMESTAMP    TRU DROP_SEGMENTS

------------------------------ ------------------------------ ---------- ---------- ---------- ------------ --- -------------

SYS                            TT                                      0          2          1 05-MAY-17    NO              0

 

1 rows selected.

可以看到，符合前面所做的操作。

2.10 关闭该功能

通过修改隐藏参数可以关闭该功能。

隐藏参数：_dml_monitoring_enabled

-关闭隐藏参数

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

 

System altered.

--插入一条数据

SQL> insert into tt select * from dba_tables where rownum=1;

 

1 row created.

--手动更新

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 ='TT';

 

TABLE_OWNER                    TABLE_NAME                        INSERTS    UPDATES    DELETES TIMESTAMP    TRU DROP_SEGMENTS

------------------------------ ------------------------------ ---------- ---------- ---------- ------------ --- -------------

SYS                            TT                                      0          2          1 05-MAY-17    NO              0

 

1 rows selected.

可以看到，记录没有变化。

2.11 打开该功能

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

 

System altered.

2.12 总结

1.create table创建的表，插入的数据不会被记录。

2.即使作了dml操作,如果没有commit，在刷新后exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO，sys.dba_tab_modifications也会存在记录。

3.收集表统计信息后，该视图里的记录会消失。

4.仅仅隐含参数_dml_monitoring_enabled可以关闭监测在11GR2下。

DBA_TAB_MODIFICATIONS

签到成功

CNDBA社区

DBA_TAB_MODIFICATIONS视图介绍说明