ORA-00942: 可以从“schema.table”中选择但不是“桌子”？
我在执行时遇到了ORA-00942（“表或视图不存在”）
select * from brunch
但是，执行时没有这样的问题
select * from joe.brunch
我可以知道这里的问题是什么吗？
I experienced an ORA-00942 ("table or view does not exist") when executing
select * from brunch
However, no such problem when executing
select * from joe.brunch
May i know what is the issue here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
不合格，
BRUNCH引用当前会话中与JOE.BRUNCH不同的对象。您有几个选项可以解决这个问题。创建公共同义词。这将允许任何拥有
JOE.BRUNCH表权限的用户通过查询BRUNCH来访问它
创建公共同义词早午餐
FOR joe.brunch
创建一个私有同义词。这将仅允许当前用户通过查询
BRUNCH来访问
JOE.BRUNCH表创建同义词早午餐
FOR joe.brunch
将当前会话的当前架构更改为
JOE。这将导致当前会话中的所有不合格引用解析为JOE架构，而不是当前用户的架构ALTER SESSION SET current_schema = JOE
Unqualified,
BRUNCHrefers to a different object thanJOE.BRUNCHin your current session. You've got a couple of options to fix that.Create a public synonym. This will allow any user that has privileges on the
JOE.BRUNCHtable to access it by queryingBRUNCHCREATE PUBLIC SYNONYM brunch
FOR joe.brunch
Create a private synonym. This will allow just the current user to access the
JOE.BRUNCHtable by queryingBRUNCHCREATE SYNONYM brunch
FOR joe.brunch
Change the current schema for the current session to
JOE. This will cause all unqualified references in the current session to resolve to theJOEschema rather than to the current user's schemaALTER SESSION SET current_schema = JOE
有几种可能的原因
1) 存在多个称为 brunch 的对象（表、视图、过程等）。 Oracle不知道你指的是哪一个。
2）最可能的原因：该表存在于 joe 模式中，但您正在以另一个尚未被授予对 joe.brunch 对象进行选择的用户身份进行连接
尝试
将 joe.brunch 上的选择授予 your_user
并尝试此操作并查看有多少对象匹配名称早午餐
选择*
来自所有对象
其中 object_type in ('TABLE','VIEW')
和 object_name = '早午餐';
There are several possible causes
1) there is more than one object (table,view, procedure, etc) called brunch. Oracle does not know which one you are referring to.
2) most likely cause: the table exists in the joe schema but you are connecting as another user who has not been granted select on the joe.brunch object
Try
Grant select on joe.brunch to your_user
and try this and see how many objects match the name brunch
select *
from all_objects
where object_type in (‘TABLE’,'VIEW’)
and object_name = ‘brunch‘;
我发现我引用的表（Flyway 的 schema_version 表）是用双引号创建的......因此无论在哪里引用它都需要双引号。
以下是 Oracle 的说法：
在实践中，这些有效：
当这不起作用时（-> ORA-00942：表或视图不存在）：
I found that the table I was referencing (Flyway's schema_version table), was created with double quotes... and thus needed double quotes wherever it was referenced.
Here's what Oracle says:
In practice, these worked:
When this didn't (-> ORA-00942: table or view does not exist):