查询 Oracle 数据库中的表列表时出现问题：“表或视图不存在”
在我的程序中，我需要访问 Oracle 11g 数据库的架构。我一直在尝试使用如下查询获取表列表：
SELECT t.TABLE_NAME, t.OWNER
FROM ALL_TABLES t
WHERE t.DROPPED = 'NO'
ORDER BY t.TABLE_NAME
查询有效，我得到了表列表。不幸的是，当使用返回的表名查询某些表时，出现以下错误：
ORA-00942: 表或视图不存在
可能导致此错误的原因是什么？难道是因为特权？
在一个单独的问题中，我也有点困惑是否可能存在两个或多个具有相同名称的表以及如何区分这两个表。我需要注意这一点吗？
我正在使用 .NET 中的 Oracle.DataAccess 提供程序连接到数据库。它是一个远程服务器，不幸的是我对它的访问非常有限。
In my program I need to access the schema of an Oracle 11g database. I have been trying to get a list of tables using a query like this:
SELECT t.TABLE_NAME, t.OWNER
FROM ALL_TABLES t
WHERE t.DROPPED = 'NO'
ORDER BY t.TABLE_NAME
The query works and I get back a list of tables. Unfortunately when querying some of the tables using the table name returned I get the following error:
ORA-00942: table or view does not exist
What could cause this error? Could it be down to privileges?
In a separate issue I am also a bit confused about whether there could potentially be two or tables with the same name and how I could distinguish between the two. Do I need to watch out for this?
I am using the Oracle.DataAccess provider in .NET to connect to the database. It is a remote server and unfortunately I have very limited access to it.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这可能取决于特权。但这可能是因为该表处于不同的模式中，并且没有同义词。如果您用所有者限定表名是否有效？即从 [owner].[table_name] 中选择？
It could be down to privileges. But it could be down to the table being in a different schema, and there not being a synonym for it. Does it work if you qualify the table name with the owner? ie select from [owner].[table_name] instead?
仅仅因为您有权访问 ALL_TABLES 视图并不意味着您实际上可以选择通过此视图检索的表。您需要在表上进行适当的授予选择才能选择它们。
2 个表可以具有相同的名称，只要它们属于不同的模式（范围实际上比表更广泛，一个模式不能有两个具有相同名称的对象（表、视图...））
Just because you have access to the ALL_TABLES view does not mean you can actually select the tables you retrieve via this view. You need the appropriate grant select on the tables to select them.
2 tables can have the same name as long as they belong to a different schema (the scope is actually broader than just tables, a schema cannot have two objects (table, view ...) that have the same name)
是的，这是一个安全功能 - 如果您没有从表中进行 SELECT 的权限，Oracle 通常甚至不会向您确认它的存在（普通用户将无法看到 select from ALL_ 中的内容，除非已授予该权限）由DBA）。
Yes, it is a security feature - if you do not have permission to SELECT from a table, Oracle will normally not even acknowledge to you that it exists (an ordinary user would not be able to see things in select from ALL_ unless that was granted by the DBA).