Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:Documents and Settingsvecentli>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 7月 13 09:32:28 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn sys/********@oravis as sysdba
已连接。
SQL> grant select any table to ap;
授权成功。
SQL>
SQL> conn ap/********@oravis;
已连接。
SQL> desc tab
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID
SQL> select count(*)
2 from tab;
COUNT(*)
----------
255
SQL> desc scott.tab
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER
SQL> select *
2 from scott.tab
3 ;
from scott.tab
*
ERROR 位于第 2 行:
ORA-00942: 表或视图不存在
SQL>
SQL> conn sys/************@oravis as sysdba
已连接。
SQL> desc tab
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER
SQL> desc scott.tab
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER
SQL> select *
2 from scott.tab;
from scott.tab
*
ERROR 位于第 2 行:
ORA-00942: 表或视图不存在
SQL>
dba权限的用户也不可以。。
只能desc其他用户的tab，而不能select其他用户的tab。。
看来不是权限的问题，难道oracle限制了这个做法？
----回过头来看看视图定义。
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# (+);
肯定这个 userenv('SCHEMAID') 破玩意在作怪！
SQL> select userenv('SCHEMAID')
2 from dual;
USERENV('SCHEMAID')
-------------------
0
SQL>
SCHEMAID的解释.
SCHEMAID returns the id of the schema for the current user. This id is used, for example, in obj$ (column owner#).
select userenv('SCHEMAID') from dual;
--可是为什么会报 表或视图不存在的错误呢？应该是未选定行才对啊?!
猜测:
根椐下面的试验，我猜测DESC时如果指定了方案限定词时是查找用户的表，视图，私有同义词或者公有同义词，而SELECT的时候，如果指定了方案限定词，不会去查找公有同义词，所以才有这样的差别。
SQL> show user
USER is "WYQ"
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
PLAN_TABLE
T
SQL> desc t
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
X NOT NULL NUMBER
Y VARCHAR2(128)
SQL> create synonym syn_t for t;
Synonym created.
SQL> conn sys/wyq as sysdba
Connected.
SQL> desc wyq.syn_t
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
X NOT NULL NUMBER
Y VARCHAR2(128)
SQL> select count(*) from wyq.syn_t;
COUNT(*)
----------
100
SQL> conn wyq/wyq
Connected.
SQL> drop synonym syn_t;
Synonym dropped.
SQL> create public synonym syn_t for t;
Synonym created.
SQL> conn sys/wyq as sysdba
Connected.
SQL> desc wyq.syn_t;
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
X NOT NULL NUMBER
Y VARCHAR2(128)
SQL> select count(*) from wyq.syn_t;
select count(*) from wyq.syn_t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
－－做个trace验证一把。
sqlplus " /as sysdba"
alter session set events '10046 trace name context forever,level 12'
;
desc sys.TT (there's no object name TT in my db)
exit
some thing in the trace file.
PARSING IN CURSOR #1 len=198 dep=1 uid=0 oct=3 lid=0 tim=2205811854802 hv=2703824309 ad=
'91cfeb40'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$
where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is nu
ll and subname is null
END OF STMT
PARSE #1:c=0,e=3248,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=2205811854790
BINDS #1:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=ffffffff7d069640 bln=22 avl=03 flg=05
value=0
bind 1: dty=1 mxl=32(02) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=32 offset=0
bfp=ffffffff7d069608 bln=32 avl=02 flg=05
value="TT"
bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=ffffffff7d0695d8 bln=24 avl=02 flg=05
value=1
EXEC #1:c=10000,e=3068,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=2205811858366
FETCH #1:c=0,e=310,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=3,tim=2205811858772
BINDS #1:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=ffffffff7d069640 bln=22 avl=02 flg=05
value=1
bind 1: dty=1 mxl=32(02) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=32 offset=0
bfp=ffffffff7d069608 bln=32 avl=02 flg=05
value="TT"
bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=ffffffff7d0695d8 bln=24 avl=02 flg=05
上面红色部分是owner# 的bind 值
1 select name,user#
2* from user$ where user# in (0,1)
SQL> /
PUBLIC 1
SYS 0
可以看出，Oracle先根据“SYS”(owner#=0) 找，没找到，于是就到“public” (owner#=1) 里找
....
--继续做select的trace。
SQL> alter session set events '10046 trace name context forever,level 12'
2 /
Session altered.
SQL> select * from sys.TT
2 /
select * from sys.TT
*
ERROR at line 1:
ORA-00942: table or view does not exist
可以方向在trace file里只找了"SYS”(owner#=0) 的object
PARSING IN CURSOR #3 len=198 dep=1 uid=0 oct=3 lid=0 tim=2206623598500 hv=2703824309 ad=
'91cfeb40'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$
where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is nu
ll and subname is null
END OF STMT
PARSE #3:c=0,e=139,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=2206623598489
BINDS #3:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=ffffffff7d068d78 bln=22 avl=01 flg=05
value=0
bind 1: dty=1 mxl=32(02) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=32 offset=0
bfp=ffffffff7d068d40 bln=32 avl=02 flg=05
value="TT"
bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=ffffffff7d068d10 bln=24 avl=02 flg=05
value=1
EXEC #3:c=0,e=621,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=2206623599523
FETCH #3:c=0,e=252,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=3,tim=2206623599848
=====================
PARSE ERROR #2:len=21 dep=0 uid=0 oct=3 lid=0 tim=2206623600042 err=942
select * from sys.TT
conclusions:
DESC时如果指定了方案限定词时是查找用户的表，视图，私有同义词或者公有同义词，而SELECT的时候，如果指定了方案限定词，不会去查找公有同义词，所以才有这样的差别。
polo t shirts