数据库测试环境版本
08:43:02 sql> select * from v$version ;
banner
--------------------------------------------------------------------------------
oracle database 11g enterprise edition release 11.2.0.3.0 - 64bit production
pl/sql release 11.2.0.3.0 - production
core 11.2.0.3.0 production
tns for linux: version 11.2.0.3.0 - production
nlsrtl version 11.2.0.3.0 - production
-- 归档关闭
08:56:03 sql> archive log list;
database log mode no archive mode
开始测试
-- 创建t1表，并初始10条记录
09:13:50 sql> create table t1 as select rownum id,'aa' name from dual connect by level <=10;
table created.
09:14:01 sql> select count(*) from t1;
count(*)
----------
10
-- 查看 dba_tab_modifications 表里的记录为空
09:14:08 sql> select * from dba_tab_modifications where table_name='t1';
no rows selected
-- 手动刷新：
09:15:30 sql> exec dbms_stats.flush_database_monitoring_info;
pl/sql procedure successfully completed.
elapsed: 00:00:00.11
-- dba_tab_modifications 记录仍然为空.
09:16:47 sql> select * from dba_tab_modifications where table_name='t1';
no rows selected
以上操作结论1：create table t1 as 操作插入的数据，dba_tab_modifications 不收录。
09:31:55 sql> insert into t1 values(11,'bb');
1 row created.
09:32:16 sql> select * from dba_tab_modifications where table_name='t1';
no rows selected
09:32:40 sql> exec dbms_stats.flush_database_monitoring_info;
pl/sql procedure successfully completed.
09:33:30 sql> col table_owner for a12
09:33:44 sql> col table_name for a12
09:36:57 sql> select table_owner,table_name,inserts,updates,deletes,timestamp,truncated,drop_segments from dba_tab_modifications where table_name='t1';
table_owner table_name inserts updates deletes timestamp tru drop_segments
------------ ------------ ---------- ---------- ---------- ------------------ --- -------------
sys t1 1 0 0 11-jun-22 no 0
09:37:22 sql> rollback;
rollback complete.
-- insert 已经回滚
09:37:59 sql> select count(*) from t1;
count(*)
----------
10
09:38:07 sql> exec dbms_stats.flush_database_monitoring_info;
pl/sql procedure successfully completed.
09:38:18 sql> select table_owner,table_name,inserts,updates,deletes,timestamp,truncated,drop_segments from dba_tab_modifications where table_name='t1';
table_owner table_name inserts updates deletes timestamp tru drop_segments
------------ ------------ ---------- ---------- ---------- ------------------ --- -------------
sys t1 1 0 0 11-jun-22 no 0
以上操作结论2：未提交的操作同样记录到表中，回滚未提交操作表中记录不撤销，仍然存在。
09:43:26 sql> insert /* append */ into t1 select rownum 11,'cc' from dual connect by level <=10;
10 rows created.
09:43:36 sql> exec dbms_stats.flush_database_monitoring_info
pl/sql procedure successfully completed.
09:43:40 sql> select table_owner,table_name,inserts,updates,deletes,timestamp,truncated,drop_segments from dba_tab_modifications where table_name='t1';
table_owner table_name inserts updates deletes timestamp tru drop_segments
------------ ------------ ---------- ---------- ---------- ------------------ --- -------------
sys t1 11 0 0 11-jun-22 no 0
以上操作结论3：对于/* append */ 插入的操作，表同样记录
09:45:23 sql> exec dbms_stats.gather_table_stats(ownname =>user,tabname =>'t1');
pl/sql procedure successfully completed.
09:47:55 sql> select table_owner,table_name,inserts,updates,deletes,timestamp,truncated,drop_segments from dba_tab_modifications where table_name='t1';
no rows selected
以上操作结论4：表一旦被分析，信息就会从视图消失。
09:54:33 sql> update t1 set name='u' where id=1;
1 row updated.
09:54:41 sql> delete from t1 where id=2;
1 row deleted.
09:54:57 sql> insert into t1 values(11,'bb');
1 row created.
09:54:58 sql> delete from t1 where id=3000;
0 rows deleted.-- 表中没有id=3000的记录，表未删除记录
09:54:59 sql> truncate table t1;
table truncated.
09:55:00 sql> exec dbms_stats.flush_database_monitoring_info
pl/sql procedure successfully completed.
09:55:52 sql> select table_owner,table_name,inserts,updates,deletes,timestamp,truncated,drop_segments from dba_tab_modifications where table_name='t1';
table_owner table_name inserts updates deletes timestamp tru drop_segments
------------ ------------ ---------- ---------- ---------- ------------------ --- -------------
sys t1 1 1 1 11-jun-22 yes 0
以上操作结论5：dml操作表记录准确无误,truncate 操作：truncated 字段为：yes
-- 隐含参数 ： _dml_monitoring_enabled
col name for a30;
col value for a10;
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'modified',4,'system_mod','false') ismod,
decode(bitand(y.ksppstvf,2),2,'true','false') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('instance') and
y.inst_id = userenv('instance') and
x.indx = y.indx and x.ksppinm ='_dml_monitoring_enabled'
order by
translate(x.ksppinm, ' _', ' ');
name value isdefault ismod isadj
------------------------------ ---------- --------- ---------- -----
_dml_monitoring_enabled true true false false
-- 修改隐含参数 ： _dml_monitoring_enabled 为 false
10:23:24 sql> alter system set "_dml_monitoring_enabled"=false scope=memory;
system altered.
10:23:35 sql> exec dbms_stats.flush_database_monitoring_info
pl/sql procedure successfully completed.
10:23:43 sql> select table_owner,table_name,inserts,updates,deletes,timestamp,truncated,drop_segments from dba_tab_modifications where table_name='t1';
-- 记录1条insert 操作
table_owner table_name inserts updates deletes timestamp tru drop_segments
------------ ------------ ---------- ---------- ---------- ------------------ --- -------------
sys t1 1 0 0 11-jun-22 no 0
10:23:46 sql> insert into t1 values(22,'d');
1 row created.
10:26:25 sql> exec dbms_stats.flush_database_monitoring_info
pl/sql procedure successfully completed.
10:26:30 sql> select table_owner,table_name,inserts,updates,deletes,timestamp,truncated,drop_segments from dba_tab_modifications where table_name='t1';
-- 还是1条insert 操作，刚才的insert 未被记录
table_owner table_name inserts updates deletes timestamp tru drop_segments
------------ ------------ ---------- ---------- ---------- ------------------ --- -------------
sys t1 1 0 0 11-jun-22 no 0
-- 参数值修改回默认：
10:26:33 sql> alter system set "_dml_monitoring_enabled"=true scope=memory;
system altered.
以上操作结论6：隐含参数："_dml_monitoring_enabled" 值为false 时，不记录修改
总结：
- 1、create table t1 as 的数据，dba_tab_modifications 不收录。
- 2、未提交的操作同样记录到表中，回滚未提交操作表中记录不撤销，仍然存在。
- 3、对于/* append */ 插入的操作，表同样记录
- 4、表一旦被分析，信息就会从视图消失。
- 5、dml操作表记录准确无误,truncate 操作：truncated 字段为：yes
- 6、隐含参数："_dml_monitoring_enabled" 值为false 时，不记录修改
- 7*、15分钟刷新未测出来。
- 后续如果新的发现还会继续更新
墨天轮文档：《dba_tab_modifications表的刷新策略测试.pdf》：https://www.modb.pro/doc/64617
欢迎点赞支持&或留言指正错误
最后修改时间：2022-06-13 11:32:07
「喜欢文章，快来给作者赞赏墨值吧」
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。