ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 视图查询报错

视图查询报错

原创 Linux操作系统 作者:space6212 时间:2019-05-05 22:51:05 0 删除 编辑

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


--查询视图
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    113
  • 访问量
    82211