oracle 查询 DBA_Tables 中列出的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1540985/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Querying tables listed in DBA_Tables
提问by Brawleon
A third party product we have at my company uses Oracle as a backend. I'm attempting to log into the Oracle database and look at the schema and data. I've logged in as sys/sysdba, created a user with a default tablespace of that created by the application, and granted the user all necessary permissions to query the structures. I've also set O7_DICTIONARY_ACCESSIBILITY to true to allow querying of the data dictionary objects.
我们公司的第三方产品使用 Oracle 作为后端。我正在尝试登录 Oracle 数据库并查看架构和数据。我已以 sys/sysdba 身份登录，使用应用程序创建的默认表空间创建了一个用户，并授予用户查询结构所需的所有权限。我还将 O7_DICTIONARY_ACCESSIBILITY 设置为 true 以允许查询数据字典对象。
After logging in as the user and querying User_Tables nothing is returned. But when I query DBA_Tables the tables I'd expect to find are returned. I'm new to Oracle so I'm not quite certain how a non-system table can be in the tablespace, but not a user_table.
以用户身份登录并查询 User_Tables 后，不会返回任何内容。但是当我查询 DBA_Tables 时，我希望找到的表被返回。我是 Oracle 的新手，所以我不太确定非系统表如何在表空间中，但不是 user_table。
More importantly, how do you query the data in these tables? Whenever I attempt a simple "Select *" from the tables I get a "table or view does not exist" error.
更重要的是，如何查询这些表中的数据？每当我尝试从表中执行简单的“选择 *”时，我都会收到“表或视图不存在”错误。
Thanks in advance.
提前致谢。
回答by Justin Cave
The default tablespace you set for a user controls what tablespace objects owned by that user are created in. It has nothing to do with what objects they can query.
您为用户设置的默认表空间控制在其中创建该用户拥有的表空间对象。它与他们可以查询的对象无关。
USER_TABLESreturns information about the tables that a particular user owns. It does not sound like your user owns any tables, so you would expect that to be empty.ALL_TABLESreturns information about the tables that a particular user has access to. If you granted the appropriate privileges, your user should see the tables in this data dictionary view.DBA_TABLESreturns information about every table in the database even if you don't necessarily have access to the underlying table.
USER_TABLES返回有关特定用户拥有的表的信息。听起来您的用户并不拥有任何表，因此您希望它是空的。ALL_TABLES返回有关特定用户有权访问的表的信息。如果您授予了适当的权限，您的用户应该会看到此数据字典视图中的表。DBA_TABLES即使您不一定有权访问基础表，也会返回有关数据库中每个表的信息。
If you are trying to query data from one of the tables, are you specifying the schema name (the OWNER column in ALL_TABLES)? If you do not own an object, you generally need to use fully qualified names to reference it, i.e.
如果您尝试从其中一个表中查询数据，您是否指定了架构名称（中的 OWNER 列ALL_TABLES）？如果你不拥有一个对象，你通常需要使用完全限定名来引用它，即
SELECT *
FROM schema_owner.table_name
You can avoid using fully qualified names if
如果出现以下情况，您可以避免使用完全限定名称
- You create a synonym (public or private) for the object
You change the
CURRENT_SCHEMAfor the session. This changes the default schema that a name is resolved under. It does not affect permissions and privileges. You can change the current schema with the commandALTER SESSION SET current_schema = new_schema_name
You would have to do this for each session the user creates-- potentially in a login trigger.
- 您为对象创建同义词（公共或私有）
您更改
CURRENT_SCHEMA了会话。这会更改名称解析的默认架构。它不会影响权限和特权。您可以使用以下命令更改当前架构更改会话集 current_schema = new_schema_name
您必须为用户创建的每个会话执行此操作 - 可能在登录触发器中。
