desc 视图存在, select却报视图不存在的原因-凯发app官方网站

my question?
==============

sql> select * from v$version;

banner
------------------------------------------------------------
oracle database 10g enterprise edition release 10.2.0.1.0 -
prod

pl/sql release 10.2.0.1.0 - production
core    10.2.0.1.0      production
tns for linux: version 10.2.0.1.0 - production
nlsrtl version 10.2.0.1.0 - production

sql> show user;
user is "igs_dst"

sql> desc igs_dst.tab
name                          null?    type
----------------------------- -------- --------------------
tname                         not null varchar2(30)
tabtype                                varchar2(7)
clusterid                              number

sql> select * from igs_dst.tab;
select * from igs_dst.tab
                      *
error at line 1:
ora-00942: table or view does not exist

===============
answer: desc时如果指定了方案限定词时是查找用户的表,视图,私有同义词或者公有同义词; 而select的时候，如果指定了方案限定词，不会去查找公有同义词，所以才有这样的差别。

===============
select tab view define in oracle:

sql> select text from sys.all_views where owner='sys' and view_name ='tab';

text
------------------------------------------------------------
select o.name,
      decode(o.type#, 2, 'table', 3, 'cluster',
             4, 'view', 5, 'synonym'), t.tab#
from sys.tab$ t, sys.obj$ o
where o.owner# = userenv('schemaid')
and o.type# >=2
and o.type# <=5
and o.linkname is null
and o.obj# = t.obj# ( )

sql> select userenv('schemaid') from dual;

userenv('schemaid')
-------------------
                507

==================
sql> create table aa(a int);

table created.

sql> create synonym syn_aa for aa;

synonym created.

sql> desc igs_dst.syn_aa;
name                          null?    type
----------------------------- -------- --------------------
a                                      number(38)

sql> select * from igs_dst.syn_aa;

no rows selected

==================
sql> drop synonym syn_aa;

synonym dropped.

sql> create public synonym syn_aa for aa;

synonym created.

sql> desc igs_dst.syn_aa;
name                          null?    type
----------------------------- -------- --------------------
a                                      number(38)

sql> select * from igs_dst.syn_aa;
select * from igs_dst.syn_aa
                      *
error at line 1:
ora-00942: table or view does not exist

more info see:

阅读(2381) | 评论(0) | 转发(0) |

上一篇：oracle pl/sql format output

下一篇：oracle create procedure error!

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们