从不同的模式查询两个表 [英] Query two tables from different schema

问题描述

我在Oracle中有两个不同的模式（例如S1，S2）和这些模式中的两个表（例如S1.Table1，S2.Table2）。我想从模式S1查询这两个表。

I have two different schemas in Oracle (say S1, S2) and two tables in those schemas (say S1.Table1, S2.Table2). I want to query these two tables from schema S1.

S1和S2都在不同的数据库中。从DB1 - Schema S1，我想这样做，

Both S1 and S2 are in different databases. From DB1 - Schema S1, I want to do something like this,

select T1.Id
  from S1.Table1 T1
     , S2.Table2 T2 
 Where T1.Id = T2.refId

我知道一种方法是为第二个模式创建DB Link，并在查询中使用它。遗憾的是，我没有创建数据库链接所需的权限。有没有一些方法来做这个没有DB链接？例如，在TOAD中，您可以比较两个模式对象。但是，再次，两个模式对象，它是一般比较。

I know one way of doing this would be to create a DB Link for the second schema and use it in the query. Sadly, I don't have the privileges necessary to create a DB link. Is there some way to do this without DB link? For example, in TOAD you can compare two schema objects. But again, two schema objects and it is general comparison.

任何想法，建议都非常感谢。提前感谢。

Any ideas, suggestions are greatly appreciated. Thanks in advance.

推荐答案

DB Links几乎是这里的游戏名称。如果你不能得到一个自己创建的，然后检查是否有任何公共DB链接，你可以使用。

DB Links are pretty much the name of the game here. If you can't get one created on your own, then check if there are any public DB links that you could use.

也有可能您的DBA愿意有一个DB链接用于在S1实例上创建S2.Table2的实例化视图。

It's also possible that your DBAs will be willing to have one of their DB Links used to create a materialized view of S2.Table2 on the S1 instance.

另一个选择可能是web服务，但我的猜测是，你会遇到更多的管理问题，比使用简单的DB链接。考虑那些只有有充分的理由没有链接（例如：两个单独的组织，不想在他们的数据库之间打开防火墙漏洞）。

Another option might be web services, but my guess is you'd run into much more administrative issues there than you would with a simple DB link. Consider those only if there are good reasons for no links (example: two separate organizations that don't want to open firewall holes between their databases).

如果没有这些，你就会进入一个非常丑陋的领域，但是你可以做一些工作。例如：

Failing those, you're getting into really ugly territory but you might be able to make something work. For example:

• 从一个可以同时读取多个连接的工具打开，并在那里加入。访问。


• 使用Toad等工具将S2.Table2复制到您自己的模式（在另一个模式中创建，然后是将数据复制到另一个模式）


• 如果您有或可以获得在两个服务器上定义的补充目录对象，则在S2中创建S2的物化视图作为外部表，该目录可以从S2写入并从S1读取。

但是，你真的不想长期维护这些解决方案。

You really don't want to maintain any of these solutions over the long term, though.

这篇关于从不同的模式查询两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！