database 如何使用位于不同模式中的 DBLINK 在 Oracle 中进行选择？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12624291/
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
How to SELECT in Oracle using a DBLINK located in a different schema?
提问by Steffen
We have an Oracle DBMS (11g) and the following configuration:
我们有一个 Oracle DBMS (11g) 和以下配置：
- A DB user "MYUSER"
- Two schemas "MYUSER" and "SCHEMA_B"
- User "MYUSER" can access "SCHEMA_B" and has READ permissions on its tables
- A public DB link "DB_LINK" located in "SCHEMA_B"
- The DB_LINK is working when using the DB user "SCHEMA_B" directly
- 数据库用户“MYUSER”
- 两个模式“MYUSER”和“SCHEMA_B”
- 用户“MYUSER”可以访问“SCHEMA_B”并对其表具有读取权限
- 位于“SCHEMA_B”中的公共数据库链接“DB_LINK”
- 直接使用 DB 用户“SCHEMA_B”时，DB_LINK 正在工作
Question: When logged on as "MYUSER", what is the correct syntax to access tables using the DB link of "SCHEMA_B"? Is it possible to do so at all?
问题：当以“MYUSER”身份登录时，使用“SCHEMA_B”的数据库链接访问表的正确语法是什么？有可能这样做吗？
I already tried several constellations, which all did not work:
我已经尝试了几个星座，但都不起作用：
select * from dual@"DB_LINK"
select * from dual@"SCHEMA_B"."DB_LINK"
select * from dual@SCHEMA_B."DB_LINK"
select * from dual@SCHEMA_B.DB_LINK
select * from SCHEMA_B.dual@DB_LINK
select * from "SCHEMA_B".dual@DB_LINK
The error message I receive is: ORA-02019. 00000 - "connection description for remote database not found"
我收到的错误消息是：ORA-02019。00000 - “未找到远程数据库的连接描述”
Thanks for any suggestion!
感谢您的任何建议！
回答by GTG
I don't think it is possible to share a database link between more than one user but not all. They are either private (for one user only) or public (for all users).
我认为不可能在多个用户之间共享数据库链接，但不是所有用户。它们要么是私有的（仅适用于一个用户），要么是公开的（适用于所有用户）。
A good way around this is to create a view in SCHEMA_B that exposes the table you want to access through the database link. This will also give you good control over who is allowed to select from the database link, as you can control the access to the view.
解决此问题的一个好方法是在 SCHEMA_B 中创建一个视图，该视图公开您要通过数据库链接访问的表。这也将使您能够很好地控制允许谁从数据库链接中进行选择，因为您可以控制对视图的访问。
Do like this:
这样做：
create database link db_link... as before;
create view mytable_view as select * from mytable@db_link;
grant select on mytable_view to myuser;
