视图查询报错

今天发现一个奇怪的现象，在查询一个视图的时候，明明有权限，但就是提示表或视图不存在

--查询视图
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

感谢老和尚的提醒！

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

转载于:http://blog.itpub.net/231499/viewspace-63728/