如何在 Oracle 查询中使用多个模式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31033611/
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 do I use multiple schemas in an Oracle query?
提问by Jayhoffa75
Problem: Needing to use data in two (or more) different schemas or connections in Oracle SQL Developer. For example: Invoice data in the finance schema, and sales data in the inventory schema. Needing to get to product info for the invoice data, via a join of a invoiced product to the product in the inventory schema.
问题：需要在 Oracle SQL Developer 中使用两个（或多个）不同模式或连接中的数据。例如：财务模式中的发票数据和库存模式中的销售数据。需要通过发票产品与库存架构中的产品的连接来获取发票数据的产品信息。
I am new to Oracle, coming from SQL Server. In T-SQL, I would just put the database name or the IP address. For example (random, made-up IP):
我是 Oracle 的新手，来自 SQL Server。在 T-SQL 中，我只会输入数据库名称或 IP 地址。例如（随机，虚构的IP）：
[555.00.12302].Sales_DB.Item_Table.Item_Price_Column
In SQL Developer for Oracle (using 11g), I cannot figure out how to call another connection or schema, in a select query, to join in a table from that schema. Is this possible?
在 SQL Developer for Oracle（使用 11g）中，我无法弄清楚如何在选择查询中调用另一个连接或模式，以加入该模式中的表。这可能吗？
回答by Justin Cave
I'm pretty sure that you don't mean "tablespace"-- you don't reference the tablespace in which an object is stored when you query it. Some objects are stored in multiple tablespaces. You might mean "schema" or you might mean "database" where a schema in Oracle is most similar to a database in SQL Server.
我很确定你的意思不是“表空间”——当你查询对象时，你没有引用存储对象的表空间。一些对象存储在多个表空间中。您可能指的是“模式”或“数据库”，其中 Oracle 中的模式与 SQL Server 中的数据库最为相似。
If you want to access a table stored in a different schema in the same database
如果要访问存储在同一数据库中不同模式中的表
select *
from schema_name.table_name
assuming you have privileges on table_namein the schema_nameschema. You can create public or private synonyms or change your session's current_schemato avoid prefixing the schema name but that's all just syntactic sugar for the fully qualified name.
假设您table_name在schema_name架构中有权限。您可以创建公共或私有同义词或更改会话的同义词current_schema以避免在模式名称前加上前缀，但这只是完全限定名称的语法糖。
If you really want to access data stored in a different database, then you'd need to create a database link. This is an object that is stored in the database that creates a connection to another database. There are lots of options when creating a database link. One option, assuming the same user exists on both databases with the same password in each
如果您真的想访问存储在不同数据库中的数据，那么您需要创建一个数据库链接。这是一个存储在数据库中的对象，用于创建到另一个数据库的连接。创建数据库链接时有很多选项。一种选择，假设两个数据库上存在相同的用户，每个数据库中的密码相同
CREATE DATABASE LINK my_link
CONNECT TO CURRENT_USER
USING '<<TNS alias of remote database>>';
SELECT *
FROM schema_name.table_name@my_link;
