SQL 从另一个 ORACLE 数据库查询表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28368876/
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
Query table from another ORACLE database
提问by Xuhaib
I have two different data base, one is DEVORADBwhich i use for development, and another one is UATORADBwhich tester use for testing. UATORADBhave the most updated data which is not in development. I want to query tables from UATORADBdatabase in DEVORADB. I was writing in DEVORADBin such a way but not getting the result:
我有两个不同的数据库，一个是DEVORADB我用于开发的，另一个是UATORADB测试人员用于测试的。UATORADB拥有尚未开发的最新数据。我想从UATORADB数据库中查询表DEVORADB。我是这样写的，DEVORADB但没有得到结果：
SELECT * FROM TABLE_NAME@UATDEVORADB.
回答by spencer7593
For Oracle,
对于甲骨文，
CREATE DATABASE LINK ...
e.g.
例如
With a database link created and tested, you can do a query (of the style you showed) to retrieve rows from a remote database.
通过创建和测试数据库链接，您可以执行查询（您显示的样式）以从远程数据库中检索行。
Reference: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5005.htm#SQLRF01205
参考：http: //docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5005.htm#SQLRF01205
FOLLOWUP
跟进
NOTE: In Oracle, the term "database" refers to the datafiles and logfiles associated with an Oracle "instance". To retrieve data from a second "database" means you need a second connection to the other database. Oracle provides a facility called a "database link". That allows a session(connection) to one database instance to connect to another database instance. (Without this facility, a client would need to create two separate connections, and would need to query the two databases separately.)
注意：在 Oracle 中，术语“数据库”是指与 Oracle“实例”关联的数据文件和日志文件。要从第二个“数据库”检索数据意味着您需要与另一个数据库建立第二个连接。Oracle 提供了一种称为“数据库链接”的工具。这允许到一个数据库实例的会话（连接）连接到另一个数据库实例。（如果没有此功能，客户端将需要创建两个单独的连接，并且需要分别查询两个数据库。）
If this question is regarding querying from two separate "schemas" within the samedatabase, as long as the user has sufficient privileges on objects in the second schema, the identifier can be qualified with the name of the schema, e.g.
如果这个问题是关于从同一个数据库中的两个单独的“模式”查询，只要用户对第二个模式中的对象有足够的权限，标识符就可以用模式的名称来限定，例如
SELECT * FROM UATDEVORADB.TABLE_NAME
To access data on a separate database, a database link can be used...
要访问单独数据库上的数据，可以使用数据库链接...
CREATE DATABASE LINK UADEVORADB
CONNECT TO user
IDENTIFIED BY password
USING 'uadevoradb' ;
(This will require an appropriate matching entry in the tnsnames.ora file on the Oracle server, or the oracle names server, or the connection details can be spelled out in place of a tnsnames.ora entry, something like:
（这将需要 Oracle 服务器或 oracle 名称服务器上的 tnsnames.ora 文件中的适当匹配条目，或者可以拼出连接详细信息来代替 tnsnames.ora 条目，例如：
CREATE DATABASE LINK UADEVORADB
CONNECT TO user IDENTIFIED BY password
USING '(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=uadevorahost1)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=uadevoradb.domaindb)))'
If the "user" specified in the database link differs from the "owner" of the table on the remote system, and there's no synonym that references the table, the table identifier will need to be qualified with the owner...
如果数据库链接中指定的“用户”与远程系统上表的“所有者”不同，并且没有引用该表的同义词，则表标识符将需要由所有者限定...
SELECT * FROM OWNER.TABLE_NAME@UADEVORADB ;
