DBA_TAB_MODIFICATIONS表的刷新策略测试

数据库测试环境版本

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
                     欢迎点赞支持&或留言指正错误