2011-12-21 11gR2 DBA_TAB_MODIFICATIONS BUG

新知识点1： 11GR2 DBA_TAB_MODIFICATIONS BUG
查询 DBA_TAB_MODIFICATIONS 遇到BUG
sys@ORCL-10.1.16.14>select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

1 row selected.

Elapsed: 00:00:00.00
sys@ORCL-10.1.16.14>select table_name,table_owner from dba_tab_modifications where table_name='DEPT';

no rows selected

Elapsed: 00:00:00.01
sys@ORCL-10.1.16.14>select table_name,table_owner from dba_tab_modifications where table_name='DEPT' and table_owner='NAT';

TABLE_NAME                     TABLE_OWNER
------------------------------ ------------------------------
DEPT                           NAT

1 row selected.

sys@ORCL-10.1.16.14>select table_name,owner from dba_tables where table_name = 'DEPT';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
DEPT                           NAT
DEPT                           SCOTT

2 rows selected.

Elapsed: 00:00:00.03
sys@ORCL-10.1.16.14>select count(*) from dba_tab_modifications where table_name = 'DEPT';

  COUNT(*)
----------
         0

1 row selected.

Elapsed: 00:00:00.01
sys@ORCL-10.1.16.14>select count(*) from dba_tab_modifications where table_name = 'DEPT' and table_owner = 'NAT';

  COUNT(*)
----------
         1

1 row selected.

Elapsed: 00:00:00.01

再次测试：

sys@ORCL-10.1.16.14>create table nat.obj as select * from dba_objects;

Table created.

Elapsed: 00:00:01.78
sys@ORCL-10.1.16.14>exec dbms_stats.gather_table_stats('NAT','OBJ');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.42
sys@ORCL-10.1.16.14>delete from nat.obj;

70623 rows deleted.

Elapsed: 00:00:00.64
sys@ORCL-10.1.16.14>commit;

Commit complete.

Elapsed: 00:00:00.03
sys@ORCL-10.1.16.14>exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
sys@ORCL-10.1.16.14>select table_name,table_owner from dba_tab_modifications where table_name = 'OBJ';

no rows selected

Elapsed: 00:00:00.01
sys@ORCL-10.1.16.14>select table_name,table_owner from dba_tab_modifications where table_name = 'OBJ' and table_owner = 'NAT';

TABLE_NAME                     TABLE_OWNER
------------------------------ ------------------------------
OBJ                            NAT

1 row selected.

Elapsed: 00:00:00.01
sys@ORCL-10.1.16.14>



另外：
nat@ORCL-10.1.16.14>select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

1 row selected.

Elapsed: 00:00:00.00
nat@ORCL-10.1.16.14>select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
NAT                            CONNECT                        NO  YES NO
NAT                            DBA                            NO  YES NO
NAT                            RESOURCE                       NO  YES NO

3 rows selected.

Elapsed: 00:00:00.00
nat@ORCL-10.1.16.14>select * from dba_tab_modifications;
select * from dba_tab_modifications
              *
ERROR at line 1:
ORA-00942: table or view does not exist

sys@ORCL-10.1.16.14>SELECT * FROM Dba_Synonyms WHERE Synonym_name = 'DBA_TAB_MODIFICATIONS'
  2  ;

no rows selected

Elapsed: 00:00:00.00
sys@ORCL-10.1.16.14>create public synonym DBA_TAB_MODIFICATIONS for DBA_TAB_MODIFICATIONS;

Synonym created.

Elapsed: 00:00:00.25
sys@ORCL-10.1.16.14>select table_name,table_owner from dba_tab_modifications where table_name = 'OBJ';

no rows selected

Elapsed: 00:00:00.00

找到相关帖子
http://www.itpub.net/thread-1306609-1-1.html
http://yangtingkun.itpub.net/post/468/500460
匿了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24383181/viewspace-713730/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24383181/viewspace-713730/