无法从 SYS.TABLES 中进行选择 - 表或视图不存在 [英] Unable to select from SYS.TABLES - Table or View does not exist

问题描述

我读过

USER_TABLES is tables which you own
ALL_TABLES is tables which own, and tables owner by other users, which you have been granted explicit access to
DBA_TABLES is all tables in the database
All three are views of the underlying SYS tables

基于以上和 Oracle Docs 中提到的 SYS.TABLES，我尝试

Based on the above and mention of SYS.TABLES in the Oracle Docs, I tried to

 select * from SYS.TABLES;

但它导致表或视图不存在的错误.我尝试使用 SYS 和另一个用户名.

but it resulted in an error that the table or view does not exist. I tried as both SYS and another username.

问题 1:SYS.TABLES 是 Oracle 11g 中的有效表吗?

Question 1: Is SYS.TABLES a valid table in Oracle 11g ?

问题 2:如果是，SYS.TABLES 是视图 DBA_TABLES 的基础表吗?

Question 2: If yes, is SYS.TABLES the underlying table for the view DBA_TABLES ?

问题 3:如果是，我需要从 SYS.TABLES 中选择什么权限?

Question 3: If yes, what privileges do I need to select from the SYS.TABLES ?

问题 4:如何找到 DBA_TABLES 等视图的基础列和表?

Question 4: How do I find the underlying column and the tables for a view such as DBA_TABLES ?

我已经编辑了问题.很抱歉我之前造成的混乱.

I have edited the question. Sorry about the confusion I caused earlier.

推荐答案

这是 11g(不过是 XE；没关系).

This is 11g (XE, though; never mind that).

当您想知道 SYS 拥有的对象时，就这样连接.

As you wonder about SYS-owned objects, connect as such.

SQL> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> connect sys as sysdba
Enter password:
Connected.

1.

TABLES 是否存在?不.

SQL> desc tables;
ERROR:
ORA-04043: object tables does not exist

但是，TAB 确实:

SQL> desc tab;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TNAME                                     NOT NULL VARCHAR2(30)
 TABTYPE                                            VARCHAR2(7)
 CLUSTERID                                          NUMBER

2.

为了找出DBA_TABLES的真正含义:

SQL> select owner, object_type from dba_objects where object_name = 'DBA_TABLES';

OWNER                          OBJECT_TYPE
------------------------------ -------------------
SYS                            VIEW
PUBLIC                         SYNONYM

4.

好的，这是一个视图.它由哪个查询组成?

4.

OK, it is a view. Which query is it made of?

SQL> set long 20000
SQL> select text from dba_views where view_name = 'DBA_TABLES';

TEXT
--------------------------------------------------------------------------------
select u.name, o.name,
       decode(bitand(t.property,2151678048), 0, ts.name,
              decode(t.ts#, 0, null, ts.name)),
       decode(bitand(t.property, 1024), 0, null, co.name),
       decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
              0, null, co.name),
       decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'),
<snip>
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
     sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi,
     sys.deferred_stg$ ds
where o.owner# = u.user#
  and o.obj# = t.obj#
  and bitand(t.property, 1) = 0
  and bitand(o.flags, 128) = 0
  and t.bobj# = co.obj# (+)
  and t.ts# = ts.ts#
<snip>

(这是一个很长的查询，所以我只显示了其中的一部分；既然您知道如何去做，请自己动手以获取更多信息).

(It is a quite long query so I displayed only some parts of it; now that you know how to do it, do it yourself for more info).

TAB 另一方面，非常简单:

TAB is, on the other hand, quite simpler:

SQL> select text from dba_views where 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."_CURRENT_EDITION_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# (+)

您将对 FROM 子句中指定的表格"使用相同的原则，例如

You'd use the same principle for "tables" specified in the FROM clause, e.g.

SQL> select owner, object_type from dba_objects where object_name = 'TAB$';

OWNER                          OBJECT_TYPE
------------------------------ -------------------
SYS                            TABLE

所以 - 是的，这就是结局.TAB$ 是决赛桌，后面什么都没有.

So - yes, that's the end. TAB$ is the final table, there's nothing behind.

如果以 SYS 身份连接，则不需要任何额外权限.SYS拥有数据库，它是老板，它可以任何事情.

If connected as SYS, you don't need any additional privileges. SYS owns the database, it is the boss, it can do anything.

对于其他用户，所有者授予权限，例如(仍以 SYS 连接):

For other users, owner grants privileges, e.g. (still connected as SYS):

SQL> grant select on tab$ to scott;

Grant succeeded.

SQL> grant select on x$ksppcv to scott;
grant select on x$ksppcv to scott
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


SQL>

我想应该是这样.

请注意:正如我所说，SYS 非常强大.小心你所做的.我希望你有一个像我一样备用的数据库；如果我在这里搞砸了，什么都不会发生，里面没有存储重要数据.不要在生产数据库上玩游戏.

Just a note: SYS is, as I said, powerful. Be careful what you do. I hope you have a database to spare like I do; nothing much will happen if I screw something here, there's no important data stored in it. Don't play games on production databases.

这篇关于无法从 SYS.TABLES 中进行选择 - 表或视图不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！