来自另一个ORACLE数据库的查询表 [英] Query table from another ORACLE database

问题描述

我有两个不同的数据库，一个是我用于开发的DEVORADB，另一个是测试人员用于测试的UATORADB. UATORADB具有尚未开发的最新数据.我想从DEVORADB中的UATORADB数据库查询表.我以这种方式在DEVORADB中编写但没有得到结果:

I have two different data base, one is DEVORADB which i use for development, and another one is UATORADB which tester use for testing. UATORADB have the most updated data which is not in development. I want to query tables from UATORADB database in DEVORADB. I was writing in DEVORADB in such a way but not getting the result:

SELECT * FROM TABLE_NAME@UATDEVORADB.

推荐答案

对于Oracle，

CREATE DATABASE LINK ...

例如

在创建并测试了数据库链接之后，您可以进行查询(显示的样式)以从远程数据库中检索行.

With a database link created and tested, you can do a query (of the style you showed) to retrieve rows from a remote database.

参考: http://docs.oracle. com/cd/E11882_01/server.112/e41084/statements_5005.htm#SQLRF01205

关注

注意:在Oracle中，术语数据库"是指与Oracle实例"相关联的数据文件和日志文件.从第二个数据库"中检索数据意味着您需要与另一个数据库的第二个连接. Oracle提供了一种称为数据库链接"的功能.这允许到一个数据库实例的会话(连接)连接到另一个数据库实例. (没有此功能，客户端将需要创建两个单独的连接，并且需要分别查询两个数据库.)

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

如果此问题是关于从 same 数据库中的两个单独的方案"进行查询，则只要用户对第二个方案中的对象具有足够的特权，则标识符可以使用名称进行限定模式的，例如

If this question is regarding querying from two separate "schemas" within the same database, 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' ;

(这将需要在Oracle服务器或oracle名称服务器上的tnsnames.ora文件中有一个适当的匹配条目，或者可以用tnsnames.ora条目代替连接详细信息，例如:

(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:

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 ;

这篇关于来自另一个ORACLE数据库的查询表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！