新知识点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
匿了
查询 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/