如何使用位于不同架构中的DBLINK在Oracle中进行SELECT? [英] How to SELECT in Oracle using a DBLINK located in a different schema?

问题描述

我们有一个Oracle DBMS(11g)和以下配置:

We have an Oracle DBMS (11g) and the following configuration:

• 数据库用户"MYUSER"
• 两个模式"MYUSER"和"SCHEMA_B"
• 用户"MYUSER"可以访问"SCHEMA_B"，并且对其表具有读取权限
• 位于"SCHEMA_B"中的公共数据库链接"DB_LINK"
• 直接使用数据库用户"SCHEMA_B"时，DB_LINK可以正常工作

问题:以"MYUSER"身份登录时，使用"SCHEMA_B"的数据库链接访问表的正确语法是什么?完全可以这样做吗?

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?

我已经尝试了几个星座，但是这些星座都不起作用:

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

我收到的错误消息是: ORA-02019. 00000-找不到远程数据库的连接描述"

The error message I receive is: ORA-02019. 00000 - "connection description for remote database not found"

感谢您的任何建议！

推荐答案

我认为不可能在多个用户之间共享数据库链接，但不能在所有用户之间共享.它们是私有的(仅用于一个用户)或公共的(对于所有用户).

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).

解决此问题的一种好方法是在SCHEMA_B中创建一个视图，该视图公开要通过数据库链接访问的表.这样，您还可以很好地控制允许谁从数据库链接中进行选择，因为您可以控制对视图的访问.

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.

这样做:

create database link db_link... as before;
create view mytable_view as select * from mytable@db_link;
grant select on mytable_view to myuser;

这篇关于如何使用位于不同架构中的DBLINK在Oracle中进行SELECT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！