Oracle 视图学习--dba_tab_modifications

今天做数据库性能测试，研究数据库的统计信息如何过期的时候，发现在dba_tab_modifications中无法查看到表的更改记录，特此记录。

试验过程如下：

--查看视图是否已经存在记录
select * from dba_tab_modifications a where a.table_NAME = 'TEST1';

--1. 创建测试表
create table test1 as select * from dba_objects where 1<>1;

--2.检查
select * from test1;

no rows selected
--3.插入数据
insert into test1  select * from dba_objects;

commit;
--4.收集统计信息
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'TEST1',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size 1',
                                no_invalidate    => FALSE,
                                degree           => 1,
                                cascade          => TRUE);
END;
/

PL/SQL procedure successfully completed.
--5.查看统计信息
select A.owner, A.table_name, A.num_distinct, A.num_nulls, A.last_analyzed
  from dba_tab_col_statistics a
 where a.owner = 'SCOTT'
   and a.table_name = 'TEST1';

OWNER                TABLE_NAME                               NUM_DISTINCT  NUM_NULLS LAST_ANALYZED
-------------------- ---------------------------------------- ------------ ---------- ------------------
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21
SCOTT                TEST1                                               0          0 16-JUN-21

15 rows selected.
 

--6.删除数据
delete from test1 where object_id > 1000;

--7.检查dba
select table_owner,table_name,inserts,updates,deletes,timestamp from dba_tab_modifications b where b.table_owner = 'SCOTT' and table_name='TEST1';
no rows selected

--8.插入数据
insert into test1  select * from dba_objects where object_id >10000 and object_id < 13000;
commit;

--9.更新数据
update test1  set  owner = 'SCOTT' ;
commit;

--10.检查表的修改记录
select table_owner,table_name,inserts,updates,deletes,timestamp from dba_tab_modifications b where b.table_owner = 'SCOTT' and table_name='TEST1';
no rows

--11.刷新监控，依然没有结果
exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

select table_owner,table_name,inserts,updates,deletes,timestamp from dba_tab_modifications b where b.table_owner = 'SCOTT' and table_name='TEST1';

no rows selected

查看mos这个可能是个bug
Querying DBA_TAB_MODIFICATIONS Does Not Return Rows Without WHERE Clause (Doc ID 2427546.1)
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]
Information in this document applies to any platform.
Symptoms
Querying DBA_TAB_MODIFICATIONS without a WHERE clause returns no rows. 
SQL> SELECT * FROM DBA_TAB_MODIFICATIONS;
no rows selected 
Cause:
This issue occurs when join factorization is enabled, UNION ALL query block has at least 3 branches and there are at least 2 join (factorization) predicates.
Solution:
Workaround:
Set the _optimizer_join_factorization parameter value to false:
alter session set "_optimizer_join_factorization" = false;
then re-run the query.
Permanent Solution:
Apply patch 13984324.
This issue is permanently fixed in RDBMS 12.1.0.1 base release.

解决方法：
"_optimizer_join_factorization"=false

SQL> alter session set "_optimizer_join_factorization" = false;
Session altered.

SQL> create table test2 as select * from dba_objects where 1<>1;
Table created.

SQL> select * from test2;
no rows selected

SQL> insert into test2  select * from dba_objects;
87031 rows created.
SQL> commit;
Commit complete.

SQL> delete from test2 where object_id > 1000;
commit;
86032 rows deleted.
Commit complete.

SQL> insert into test2  select * from dba_objects where object_id >10000 and object_id < 13000;
commit;
2999 rows created.
Commit complete.

SQL> update test2  set  owner = 'SCOTT' ;
commit;
3998 rows updated.
Commit complete.

SQL> select table_owner,table_name,inserts,updates,deletes,timestamp from dba_tab_modifications b where b.table_owner = 'SCOTT' and table_name='TEST2';
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 from dba_tab_modifications b where b.table_owner = 'SCOTT' and table_name='TEST2';
TABLE_OWNER          TABLE_NAME                                  INSERTS    UPDATES    DELETES TIMESTAMP
-------------------- ---------------------------------------- ---------- ---------- ---------- ------------------
SCOTT                TEST2                                         90030       3998      86032 16-JUN-21