檢視查詢報錯

今天發現一個奇怪的現象，在查詢一個檢視的時候，明明有許可權，但就是提示表或檢視不存在

--查詢檢視
SQL> select * from dba_tab_modifications;

select * from dba_tab_modifications

ORA-00942: 表或檢視不存在

--覺得可能是授權問題，於是進行授權
SQL> conn sys/space6212 as sysdba
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as SYS

SQL> grant select on dba_tab_modifications to suk;

Grant succeeded

SQL> grant select any dictionary to suk;

Grant succeeded

--授權後查詢仍然報錯
SQL> conn suk/suk
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as suk

SQL> select * from dba_tab_modifications;

select * from dba_tab_modifications

ORA-00942: 表或檢視不存在

--查詢加上schema後就能查詢了，注意，sys.dba_tab_modifications也是檢視
SQL> select * from sys.dba_tab_modifications;

TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- ---------
SSK ITEM 0 889328 0 2006-7-19 1 NO
SSK PROCEDURE_ERR_RECORD 8 0 0 2006-7-19 1 NO
SUK CATEGORY 0 0 1 2006-8-8 13 NO
SUK ITEM 0 0 215 2006-8-8 13 NO
SUK PLAN_TABLE 84 0 84 2006-9-18 1 NO
SUK T 0 2 0 2006-9-19 1 YES

6 rows selected

--我們知道，一般情況下，我們在訪問DBA_、ALL_、USER_這些資料字典時，如果不加schema，則訪問的都是公用同義詞，難道DBA_TAB_MODIFICATIONS這個檢視沒有建立公用的同義詞？
SQL> select view_name from dba_views where view_name like 'DBA%' or view_name like 'USER%' or view_name like 'ALL%' minus select SYNONYM_NAME from dba_synonyms ;

VIEW_NAME
------------------------------
ALL_PROBE_OBJECTS
DBA_ANALYZE_OBJECTS
DBA_ATTRIBUTE_TRANSFORMATIONS
DBA_CACHEABLE_NONTABLE_OBJECTS
DBA_CACHEABLE_TABLES_BASE
DBA_FREE_SPACE_COALESCED_TMP1
DBA_FREE_SPACE_COALESCED_TMP2
DBA_FREE_SPACE_COALESCED_TMP3
DBA_IAS_CONSTRAINT_EXP
DBA_IAS_GEN_STMTS_EXP
DBA_IAS_OBJECTS_BASE
DBA_IAS_OBJECTS_EXP
DBA_IAS_POSTGEN_STMTS
DBA_IAS_PREGEN_STMTS
DBA_TAB_MODIFICATIONS
DBA_TRANSFORMATIONS

16 rows selected

--或者查詢publicsyn也可以知道
SQL> select * from publicsyn where sname='DBA_TAB_MODIFICATIONS';

--從查詢結果可以知道，oracle果然沒有為DBA_TAB_MODIFICATIONS建立公用同義詞。為sys.DBA_TAB_MODIFICATIONS建立一個同義詞即可。
SQL> conn sys/space6212 as sysdba
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as SYS

SQL> create public synonym DBA_TAB_MODIFICATIONS for sys.DBA_TAB_MODIFICATIONS;

Synonym created

SQL> conn suk/suk
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as suk

SQL> select * from dba_tab_modifications;

TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- ---------
SSK ITEM 0 889328 0 2006-7-19 1 NO
SSK PROCEDURE_ERR_RECORD 8 0 0 2006-7-19 1 NO
SUK CATEGORY 0 0 1 2006-8-8 13 NO
SUK ITEM 0 0 215 2006-8-8 13 NO
SUK PLAN_TABLE 84 0 84 2006-9-18 1 NO
SUK T 0 2 0 2006-9-19 1 YES

6 rows selected

感謝老和尚的提醒！