oracle 在不指定模式名称的情况下访问表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21846648/
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
Accessing a table without specifying the schema name
提问by Ashok Kumar Dabbadi
I have a schema called GBO_ARC_SCHEMA, in which I have one table called TEST_EMP,
and I have two users say USER_Aand USER_B.
我有一个名为 的模式GBO_ARC_SCHEMA，其中有一个名为的表TEST_EMP，并且有两个用户说USER_A和USER_B。
First I connected to USER_Aand fired below query
首先，我连接到USER_A并在查询下方触发
select count(*)from TEST_EMP;
count
-----
20
After that I connected as USER_band fired below query but it is giving an error, saying that table or view does not exit
之后，我连接为USER_b并在查询下方触发，但它给出了一个错误，说表或视图没有退出
select count(*)from TEST_EMP;
But if I use scma.object nameit is allowing me to query like below
但是如果我使用scma.object name它允许我像下面这样查询
select count(*)from GBO_ARC_SCHEMA.TEST_EMP;
but as per my requirement I don't want to specify schema name.
但根据我的要求，我不想指定架构名称。
can somebody help me out?
有人可以帮我吗？
回答by René Nyffenegger
If you want allusers to be able to select from the table without qualifying with the schema name, you want to create a public synonym:
如果您希望所有用户都能够从表中进行选择而无需使用架构名称进行限定，您需要创建一个公共同义词：
create public synonym TEST_EMP for GBO_ARC_SCHEMA.TEST_EMP;
If you only want user_bto omit the schema name, you want to create a private synonymWITHIN user_b's schema (that is logged on as user_b)
如果您只想user_b省略架构名称，您想要创建一个私有同义词WITHIN user_b 的架构（即以 user_b 身份登录）
create synonym TEST_EMP for GBO_ARC_SCHEMA.TEST_EMP;
If you insiston not using synonyms, then, after logging in, do a
如果你坚持不使用同义词，那么，登录后，做一个
alter session set current_schema = GBO_ARC_SCHEMA;
回答by Jeremiah Musili
On the server roles for the login you are using, simply uncheck sysadmin and serveradmin roles. That should solve it
在您使用的登录的服务器角色上，只需取消选中 sysadmin 和 serveradmin 角色。那应该可以解决
回答by Abdelhak Marouane
For Postgres users with the same problem
对于有同样问题的 Postgres 用户
ALTER ROLE <YOUR_USERNAME> SET search_path TO GBO_ARC_SCHEMA;
