database 从不同的模式查询两个表

Query two tables from different schema

提问by Guru

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.

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

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

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

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

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.

我知道这样做的一种方法是为第二个模式创建一个数据库链接并在查询中使用它。遗憾的是，我没有创建数据库链接所需的权限。有没有办法在没有数据库链接的情况下做到这一点？例如，在 TOAD 中，您可以比较两个架构对象。但同样，两个模式对象和它是一般比较。

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

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

采纳答案by Jim Hudson

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.

DB Links 几乎是这里的游戏名称。如果您无法自行创建，请检查是否有任何可以使用的公共数据库链接。

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.

也有可能您的 DBA 愿意使用他们的数据库链接之一在 S1 实例上创建 S2.Table2 的物化视图。

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

另一种选择可能是 Web 服务，但我猜您在那里遇到的管理问题比使用简单的数据库链接要多得多。只有在没有链接的充分理由时才考虑那些（例如：不想在其数据库之间打开防火墙漏洞的两个独立组织）。

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

如果做不到这些，您将进入非常丑陋的领域，但您也许可以做出一些努力。例如：

• Open up both from a tool that can read from multiple connections at once and do the join there. Access. Toad for Data Analysis, whatever.
• Use a tool like Toad to copy S2.Table2 to your own schema ("create in another schema" followed by "copy data to another schema")
• If you have, or can get, complementary directory objects defined on both servers, create a Materialized View of S2 as an external table in a directory which can be written from S2 and read from S1.

• 从可以同时读取多个连接的工具中打开两者并在那里进行连接。使用权。用于数据分析的蟾蜍，无论如何。
• 使用 Toad 之类的工具将 S2.Table2 复制到您自己的架构（“在另一个架构中创建”，然后是“将数据复制到另一个架构”）
• 如果您拥有或可以获得在两台服务器上定义的互补目录对象，请创建 S2 的实体化视图作为目录中的外部表，该目录可以从 S2 写入并从 S1 读取。

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

不过，您真的不想长期维护这些解决方案中的任何一个。

回答by Vincent Malgrat

You won't need a database link if the two schemas are in the same database.

如果两个模式在同一个数据库中，则不需要数据库链接。

Your query should work from schema S1, provided S1 has been granted the SELECT privilege on S2.table2 (from a dba account or from the S2 schema: GRANT SELECT ON S2.Table2 TO S1).

如果 S1 已被授予对 S2.table2 的 SELECT 特权（来自 dba 帐户或来自 S2 架构：），您的查询应该从架构 S1 工作GRANT SELECT ON S2.Table2 TO S1。

回答by tuinstoel

You could create a java stored proc that connects to the other database and executes a select on the other database via JDBC. The java stored proc has to return a collection. You could join this collection via a select from table(...)with the table in your own database.

您可以创建一个 java 存储过程，该过程连接到另一个数据库并通过 JDBC 在另一个数据库上执行选择。java存储过程必须返回一个集合。您可以通过select from table(...)与您自己数据库中的表一起加入此集合。

See herefor a roughly similar solution.

有关大致相似的解决方案，请参见此处。

I think this approach will be slow and complicated because you have to do a lot of coding and you have to create a pl/sql wrapper for your java stored proc.

我认为这种方法会缓慢而复杂，因为您必须进行大量编码，并且必须为 Java 存储过程创建一个 pl/sql 包装器。

It is better to create a database link.

最好创建一个数据库链接。

回答by Steve Broberg

You don't specify whether this feature is needed as part of production code, or if you're trying to join the two tables to perform some one-time analysis. If it's the latter, you can use Microsoft Access to create a local mdb file that contains linked tables to the two databases, then write a local Access query that refers to those two tables. You could then use that mdb as a data source for various reporting tools.

您没有指定此功能是否需要作为生产代码的一部分，或者您是否尝试连接两个表以执行一些一次性分析。如果是后者，您可以使用 Microsoft Access 创建一个本地 mdb 文件，其中包含两个数据库的链接表，然后编写一个引用这两个表的本地 Access 查询。然后，您可以使用该 mdb 作为各种报告工具的数据源。

The queries might not use indexes as efficiently as a native Oracle db link, but it would be better than nothing.

查询使用索引的效率可能不如原生 Oracle 数据库链接，但总比没有好。

edit: Nevermind - I see that this was already suggested above.

编辑：没关系 - 我看到上面已经建议了。

回答by KM.

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database.

使用 CREATE DATABASE LINK 语句创建数据库链接。数据库链接是一个数据库中的模式对象，使您可以访问另一个数据库中的对象。

a little off topic, but you might want to use the newer join syntax:

有点离题，但您可能想使用较新的连接语法：

SELECT
    T1.Id
    FROM S1.Table1            T1
        INNER JOIN S2.Table2  T2 ON T1.Id = T2.refId

All join conditions appear in the "ON" clause, and filter conditions appear in the "WHERE".

所有连接条件都出现在“ON”子句中，过滤条件出现在“WHERE”中。

This new style makes LEFT/RIGHT joins easier to read and understand. Also, I'm not that familiar with Oracle (it has been many years since I worked on it), but with SQL Server, I've seen problems when the old join style and new join style were mixed together in a query using views.

这种新样式使 LEFT/RIGHT 连接更易于阅读和理解。另外，我对 Oracle 不太熟悉（自从我从事它已经很多年了），但是对于 SQL Server，当旧的连接样式和新的连接样式在使用视图的查询中混合在一起时，我看到了问题.