數(shù)據(jù)庫(kù)測(cè)試環(huán)境版本

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
-- 歸檔關(guān)閉
08:56:03 SQL> archive log list;
Database log mode              No Archive Mode

開始測(cè)試

-- 創(chuàng)建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
-- 手動(dòng)刷新：
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

以上操作結(jié)論1：create table t1 as 操作插入的數(shù)據(jù)，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 已經(jīng)回滾
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

以上操作結(jié)論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

以上操作結(jié)論3：對(duì)于/*+ 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

以上操作結(jié)論4：表一旦被分析，信息就會(huì)從視圖消失。

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

以上操作結(jié)論5：dml操作表記錄準(zhǔn)確無誤,truncate 操作：TRUNCATED 字段為：YES

-- 隱含參數(shù) ： _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
-- 修改隱含參數(shù) ： _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
-- 參數(shù)值修改回默認(rèn)：
10:26:33 SQL> alter system set "_dml_monitoring_enabled"=true scope=memory;
System altered.

以上操作結(jié)論6：隱含參數(shù)："_dml_monitoring_enabled" 值為false 時(shí)，不記錄修改

總結(jié)：

• 1、create table t1 as 的數(shù)據(jù)，DBA_TAB_MODIFICATIONS 不收錄。
• 2、未提交的操作同樣記錄到表中，回滾未提交操作表中記錄不撤銷，仍然存在。
• 3、對(duì)于/*+ append */ 插入的操作，表同樣記錄
• 4、表一旦被分析，信息就會(huì)從視圖消失。
• 5、dml操作表記錄準(zhǔn)確無誤,truncate 操作：TRUNCATED 字段為：YES
• 6、隱含參數(shù)："_dml_monitoring_enabled" 值為false 時(shí)，不記錄修改
• 7*、15分鐘刷新未測(cè)出來。
• 后續(xù)如果新的發(fā)現(xiàn)還會(huì)繼續(xù)更新
  墨天輪文檔：《DBA_TAB_MODIFICATIONS表的刷新策略測(cè)試.pdf》：http://m.sunline.cc/doc/64617
  ???????????????????歡迎點(diǎn)贊支持&或留言指正錯(cuò)誤