Oracle配置dblink访问PostgreSQL的操作方法_oracle

Oracle dblink的底层是通过ODBC连接PostgreSQL执行SQL的，需安装unixODBC和PostgreSQL ODBC驱动（它们的配置文件是：odbcinst.ini和odbc.ini），还需配置Oracle网络使用这个驱动。以下操作都是在Oracle服务器上进行：

以root用户安装unixODBC：

yum install unixODBC unixODBC-devel.x86_64

unixODBC相当于Linux中管理所有数据库ODBC驱动的管理器。

以root用户安装PostgreSQL ODBC驱动
编译安装，以root执行：

yum install libpq5-devel.x86_64
wget https://ftp.postgresql.org/pub/odbc/versions/src/psqlodbc-12.02.0000.tar.gz
tar xf psqlodbc-12.02.0000.tar.gz
cd psqlodbc-12.02.0000
./configure
make
make install

默认安装到/user/local/lib

配置odbcinst.ini和odbc.ini，以root用户执行：

vim /etc/odbcinst.ini（不区分大小写）

/usr/local/lib/psqlodbcw.so 是PostgreSQL ODBC驱动的库。
/usr/lib64/libodbcpsqlS.so 是unixODBC的库。

vim /etc/odbc.ini

这里Driver应与odbcinst.ini中的[PostgreSQL]对应，可以取任何名字，但两者需要相同。其它参数视具体PostgreSQL服务器而定。[PG]是一个连接PostgreSQL的配置名称，其它应用程序（如Oracle）就是引用这个名称访问PostgreSQL。
测试一下这个ODBC连接能否工作:

配置Oracle网络，以oracle用户执行，涉及3个Oracle配置文件的修改：

$ORACLEHOME/network/admin/listener.ora
$ORACLEHOME/hs/admin/init.ora
$ORACLEHOME/network/admin/tnsnames.ora

1）在listener.ora中，增加一项配置（红圈部分），它代表PostgreSQL提供的数据库服务，Oracle将PostgreSQL也看成一个Oracle实例，SID_NAME = PGINSTANCE是给它定义一个实例名，这个名称任意，但是这个名称决定了第二个配置文件$ORACLEHOME/hs/admin/init.ora的文件名，例如initPGINSTANCE.ora。

2）$ORACLEHOME/hs/admin/init.ora
实例PGINSTANCE连接PostgreSQL数据库时，Oracle会到$ORACLEHOME/hs/admin/下找名为initPGINSTANCE.ora的配置文件，里面是关于ODBC连接的信息，Oracle使用这些信息连接PostgreSQL，例如，在我的环境中，这个文件内容如下：

HS_FDS_CONNECT_INFO = PG 这个参数指向odbc.ini文件中的ODBC连接名称[PG]，参考前面。HS_FDS_SHAREABLE_NAME =/usr/local/lib/psqlodbcw.so 这个参数设置PostgreSQL ODBC驱动的路径，与odbcinst.ini中Driver64相同。
如果PostgreSQL的字符编码是UTF8，那么下面两个参数应该这样设置：

HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

3）在tnsnames.ora中，增加一项（红圈）：

pglink是客户端到PostgreSQL实例的连接配置，只要注意一点：“SID = PGINSTANCE”中，SID应设置为listener.ora中PostgreSQL的实例名，例如这里就是PGINSTANCE。pglink这个名称，将在创建dblink时使用。

创建dblink
在sqlplus或其它终端执行

create database link dl_pgsql connect to "postgres" identified by "post123" using 'pglink';

其中'pglink'是tnsnames.ora文件里定义的，这里把Oracle数据库作为客户端，去连接pglink所指的实例。
"postgres" identified by "post123" 是指PostgreSQL中的用户。
这各名称为dl_pgsql的dblink所连接的PostgreSQL数据库名由odbc.ini的Database参数指定。postgres用户应该对Database所指的数据库，有足够访问权限。

通过dblink访问PostgreSQL
假设emp表在名为postgres的数据库中的名为public的schema下，Oracle完成上面的配置后，可以这样访问：

select * from "public"."emp"@dl_pgsql;

创建和使用别名

CREATE SYNONYM emp_table FOR "public"."emp"@dl_pgsql;
select * from emp_table;