oracle数据库之跨库查询

本文详细介绍如何通过创建DBLink实现两台不同Oracle服务器之间的数据同步。包括DBLink的创建、使用同义词简化跨库查询、以及如何删除创建的DBLink等关键步骤。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

目的:

想要从一台电脑上的oracle数据库中读取数据,然后插入到另一台电脑的oracle数据库中,如:要复制的数据来源电脑oracle服务器的实例名:orcl,IP地址:192.168.125.44,将其复制到192.168.125.22电脑上的数据库中

(一)准备工作

连接上要插入数据的oracle数据库(192.168.125.22上,此处连接方式很简单,不详细介绍),创建一个DBLINK。

create public database link dblink_snms1(自定义名称)

       connect to NVDUSER1 identified by NVDUSER2 
       using '(DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.222)(PORT = 1521))
                )
                (CONNECT_DATA =
                  (SERVICE_NAME = orcl)
                )

              )';

参数介绍

dblink_snms1(自定义名称)

(NVDUSER1 为用户名,NVDUSER2为密码,192.168.125.44上的数据库的用户)

(orcl为192.168.125.44)



(二)连接跨库的数据

--(查询192.168.125.44上的AD_HP表中数据)

SELECT * FROM AD_HP@DBLINK_SNMS1;

  也可以为这个表创建一个同义词
  create synonym aaa for AD_HP@DBLINK_SNMS1;

  如下语句的效果和上面的一样
  select * from aaa;


  删除同义词的语句为

  drop synonym aaa;


(三)删除创建的DBLINK

drop public database link dblink_snms1;

(四)  查看当前数据库的DB Link;

      select * from user_db_links; --用户 DB Link
      select * from dba_db_links;  --dba DB Link
      select * from v$dblink;      --当前DB Link







<think>我们被要求使用中文回答,并且根据用户的问题,用户想知道在PostgreSQL中如何查询Oracle数据库。 根据提供的引用,我们注意到引用[1]和引用[3]提到了PostgreSQL查询的方法,但都是针对PostgreSQL数据库之间的查询,并没有直接提到Oracle。 然而,引用[3]中提到了使用外部数据封装器(FDW)的方法,这是PostgreSQL提供的一种访问外部数据源的标准机制。对于Oracle数据库,我们可以使用Oracle FDW(oracle_fdw)扩展来实现。 因此,解决方案是使用oracle_fdw扩展。以下是实现步骤: 1. 安装oracle_fdw扩展 需要先安装oracle_fdw扩展,这个扩展需要编译安装,并且需要Oracle的客户端(如OCI)。 2. 创建扩展 在PostgreSQL数据库中创建扩展。 3. 创建外部服务器 定义一个外部服务器,指向Oracle数据库的连接信息。 4. 创建用户映射 为当前PostgreSQL用户创建到Oracle用户的映射。 5. 创建外部表 在PostgreSQL中创建外部表,映射到Oracle数据库中的表。 6. 查询外部表 然后就可以像查询本地表一样查询外部表了。 具体步骤如下: 步骤1:安装oracle_fdw 需要从github下载oracle_fdw的源码(https://github.com/laurenz/oracle_fdw)进行编译安装。 注意:编译安装需要Oracle Instant Client或完整Oracle客户端的头文件和。 步骤2:在PostgreSQL中创建扩展 ```sql CREATE EXTENSION oracle_fdw; ``` 步骤3:创建外部服务器 ```sql CREATE SERVER oracle_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//host:port/service_name'); ``` 其中,'//host:port/service_name' 替换为你的Oracle数据库连接字符串。 步骤4:创建用户映射 ```sql CREATE USER MAPPING FOR postgres SERVER oracle_server OPTIONS (user 'oracle_user', password 'oracle_password'); ``` 这里,将PostgreSQL的当前用户(例如postgres)映射到Oracle数据库的用户(oracle_user)和密码(oracle_password)。 步骤5:创建外部表 ```sql CREATE FOREIGN TABLE oracle_table ( id integer, name varchar ) SERVER oracle_server OPTIONS (schema 'SCHEMA_NAME', table 'TABLE_NAME'); ``` 注意:需要将SCHEMA_NAME和TABLE_NAME替换为Oracle数据库中的模式名和表名。字段类型需要映射到PostgreSQL的类型。 步骤6:查询 ```sql SELECT * FROM oracle_table; ``` 注意事项: - 需要确保PostgreSQL服务器能够访问Oracle数据库服务器。 - 类型映射:Oracle和PostgreSQL的数据类型不完全相同,创建外部表时需要注意类型的映射。 - 性能:数据库查询可能会有性能影响,特别是大数据量的查询。 引用[3]中提到了使用postgres_fdw,而这里我们使用oracle_fdw,原理类似,只是针对Oracle数据库。 因此,用户可以通过安装并配置oracle_fdw扩展来实现从PostgreSQL查询Oracle数据库。 相关问题: 1. 如何安装oracle_fdw扩展? 2. 在创建外部表时,如何映射Oracle和PostgreSQL的数据类型? 3. 使用oracle_fdw进行查询的性能如何优化?</think>### PostgreSQL 查询 Oracle 数据库的方法 在 PostgreSQL 中查询 Oracle 数据库,可通过以下两种主流方案实现: --- ### 方案一:使用 **`oracle_fdw` 扩展**(推荐) 这是专为 Oracle 设计的 PostgreSQL 外部数据封装器(Foreign Data Wrapper),提供原生级集成。 #### 实现步骤: 1. **安装依赖** ```bash # 安装 Oracle Instant Client(基础) wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basic-linuxx64.zip unzip instantclient-basic-linuxx64.zip # 编译安装 oracle_fdw 扩展 git clone https://github.com/laurenz/oracle_fdw.git cd oracle_fdw && make && sudo make install ``` 2. **在 PostgreSQL 中启用扩展** ```sql CREATE EXTENSION oracle_fdw; -- 创建 FDW 扩展 ``` 3. **配置 Oracle 连接** ```sql -- 创建服务器定义(替换实际参数) CREATE SERVER oracle_db FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//oracle_host:1521/ORCL'); -- 创建用户映射(Oracle 凭据) CREATE USER MAPPING FOR postgres SERVER oracle_db OPTIONS (user 'oracle_user', password 'oracle_pass'); ``` 4. **创建外部表映射** ```sql -- 映射 Oracle 的 SCOTT.EMP 表 CREATE FOREIGN TABLE pg_oracle_emp ( empno integer, ename text, job text ) SERVER oracle_db OPTIONS (schema 'SCOTT', table 'EMP'); ``` 5. **执行查询** ```sql SELECT * FROM pg_oracle_emp WHERE job = 'MANAGER'; -- 直接查询 Oracle 数据 ``` > **优势**:高性能、支持事务、类型自动转换[^3]。 --- ### 方案二:通过 **ODBC 桥接** 适用场景:无法直接安装 `oracle_fdw` 的环境。 #### 实现步骤: 1. **配置 ODBC 驱动** ```ini # /etc/odbc.ini [ORACLE_SOURCE] Driver = Oracle ODBC Driver ServerName = //oracle_host:1521/ORCL UserID = oracle_user Password = oracle_pass ``` 2. **使用 `odbc_fdw` 扩展** ```sql CREATE EXTENSION odbc_fdw; CREATE SERVER oracle_odbc FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'ORACLE_SOURCE'); CREATE FOREIGN TABLE odbc_emp (...) SERVER oracle_odbc OPTIONS (sql 'SELECT * FROM SCOTT.EMP'); ``` --- ### 关键对比 | 特性 | `oracle_fdw` | ODBC 桥接 | |---------------------|----------------------|----------------| | **性能** | ⭐⭐⭐⭐ (原生协议) | ⭐⭐ (中间层转换) | | **安装复杂度** | 中等(需编译) | 简单 | | **数据类型支持** | 完整自动映射 | 需手动转换 | | **事务支持** | 完整 ACID | 部分支持 | > **推荐场景**:生产环境优先选用 `oracle_fdw`;临时需求可用 ODBC 方案[^1][^3]。 --- ### 注意事项 1. **防火墙**:确保 PostgreSQL 服务器能访问 Oracle 的监听端口(默认 1521)。 2. **权限**:Oracle 用户需赋予 `SELECT` 目标表的权限。 3. **时区处理**:若涉及时间字段,需用 `AT TIME ZONE` 统一时区: ```sql SELECT hire_date AT TIME ZONE 'UTC' FROM pg_oracle_emp; ``` ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值