oracle通过dblink 连接pg数据库

1.Oracle服务器安装odbc(全在oracle服务端操作）

yum install -y unixODBC unixODBC-devel postgresql-odbc

2.验证odbc安装

[oracle@pg01 admin]$ odbcinst -q -d
[PostgreSQL]
[MySQL]
[FreeTDS]
[MariaDB]

[oracle@pg01 admin]$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

3.配置odbc源(按照自己所需配置）

vi /etc/odbc.ini
[appdb]
Description = PostgresSQLODBC
Driver = /usr/lib64/psqlodbc.so
Database = appdb
Servername = 192.168.75.131
UserName = appuser
Password = 1qaz@WSX
Port = 5666
ReadOnly = 0
ConnSettings = set client_encoding to UTF8

4.测试ODBC连接

[root@pg01 pgdata]# isql -v appdb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from employee;
+---------------------+
| count               |
+---------------------+
| 3                   |
+---------------------+

5.创建配置文件

vi /opt/oracle/product/23ai/dbhomeFree/hs/admin/initappdb.ora

HS_FDS_CONNECT_INFO =appdb
set ODBCINI = /etc/odbc.ini
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME =/usr/lib64/libodbc.so
HS_NLS_NCHAR = UCS2
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
HS_RPC_FETCH_REBLOCKING=OFF
HS_FDS_FETCH_ROWS=1

注意：initappdb.ora 该名字要同odbc.ini 中的数据源名称如[appdb]

6.配置tns与监听

vi /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
appdb =
  (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.75.129)(PORT=1522))
        (CONNECT_DATA=(SID=appdb))
        (HS=OK)
)


vi /opt/oracle/product/23ai/dbhomeFree/network/admin/listener.ora
LISTENER_HS =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pg01)(PORT = 1522))
    )
  )

SID_LIST_LISTENER_HS=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=appdb)
         (ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree)
         (PROGRAM=dg4odbc)
      )
  )

7.启动监听

[oracle@pg01 admin]$ lsnrctl start  LISTENER_HS

LSNRCTL for Linux: Version 23.0.0.0.0 - Production on 19-JUN-2025 13:18:53

Copyright (c) 1991, 2024, Oracle.  All rights reserved.

Starting /opt/oracle/product/23ai/dbhomeFree/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 23.0.0.0.0 - Production
System parameter file is /opt/oracle/product/23ai/dbhomeFree/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/pg01/listener_hs/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pg01)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pg01)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_HS
Version                   TNSLSNR for Linux: Version 23.0.0.0.0 - Production
Start Date                19-JUN-2025 13:18:53
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/23ai/dbhomeFree/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/pg01/listener_hs/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pg01)(PORT=1522)))
Services Summary...
Service "appdb" has 1 instance(s).
  Instance "appdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

8.oracle 中创建dblink

SQL> create database link a connect to "appuser" identified by "1qaz@WSX" using 'appdb';

Database link created.

9.验证查询

SQL> select count(1) from "employee"@a;

  COUNT(1)
----------
	 3

注意：如遇如下报错可opt/oracle/product/23ai/dbhomeFree/hs/log 排查相关错误信息

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from A