1. 概述

Oracle_fdw是PG的一個(gè)外部數(shù)據(jù)接口插件,，可以使PostgreSQL輕松跨庫(kù)操作Oracle，Oracle_fdw的作用有以下兩點(diǎn)：

• 可以使PG中的表和Oracle中表/視圖作Join查詢,，類似dblink的功能,。
• 此插件實(shí)現(xiàn)了外部表的更新接口，故通過(guò)Oracle_fdw在PG數(shù)據(jù)庫(kù)中可以跨庫(kù)增刪改Oracle中的表
• 使用此插件可以方便的把數(shù)據(jù)在PostgreSQL與Oracle中進(jìn)行相互導(dǎo)數(shù)據(jù),。

Oracle_fdw的源代碼在github上：

• https://github.com/laurenz/oracle_fdw

在所有的第三方的FDW插件中,，Oracle_fdw是一個(gè)質(zhì)量比較高的插件，一直也有人在維護(hù),。

2. 配置Oracle環(huán)境

Oracle_fdw 的編譯依賴系統(tǒng)中需要有pg_config和Oracle的環(huán)境,，如果沒有，就很難安裝成功,，需要安裝一個(gè),。

2.1 安裝Oracle客戶端

為了方便我們安裝Oracle的簡(jiǎn)版客戶端instantclient，當(dāng)然安裝完全版本的Oracle 客戶端程序也是可以的,。

• 從官網(wǎng)下載 instantclient-basic-linux.x64-12.2.0.1.0.zip,、 instantclient-sdk-linux.x64-12.2.0.1.0.zip、 instantclient-sqlplus-linux.x64-12.2.0.1.0.zip三個(gè)文件包,，并放到/opt/oracle/目錄下

mv instantclient-basic-linux.x64-12.2.0.1.0.zip instantclient-sdk-linux.x64-12.2.0.1.0.zip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip /opt/oracle/

• 解壓三個(gè)文件包

unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip
unzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip

• 解壓后會(huì)生成instantclient_12_2 目錄,，將其更名為instantclient

mv instantclient_12_2 instantclient

3. 下載和編譯安裝

進(jìn)入解壓目錄

[root@pg01 opt]# cd oracle_fdw-2.0.0/
[root@pg01 oracle_fdw-2.0.0]# ls
CHANGELOG  LICENSE   META.json                 oracle_fdw--1.1.sql  oracle_fdw.control  oracle_gis.c    README.oracle_fdw  TODO
expected   Makefile  oracle_fdw--1.0--1.1.sql  oracle_fdw.c         oracle_fdw.h        oracle_utils.c  sql

3.1 make

[root@pg01 oracle_fdw-2.0.0]# make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/sdk/include -I/oci/include -I/rdbms/public -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_fdw.o oracle_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/sdk/include -I/oci/include -I/rdbms/public -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_utils.o oracle_utils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/sdk/include -I/oci/include -I/rdbms/public -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_gis.o oracle_gis.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/pgsql-9.6/lib -Wl,--as-needed  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.6/lib',--enable-new-dtags  -L -L/bin -L/lib -lclntsh -L/usr/lib/oracle/12.2/client/lib -L/usr/lib/oracle/12.2/client64/lib -L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib -L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib

3.2 make install

[root@pg01 oracle_fdw-2.0.0]# make install
/usr/bin/mkdir -p '/usr/pgsql-9.6/lib'
/usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-9.6/doc/extension'
/usr/bin/install -c -m 755  oracle_fdw.so '/usr/pgsql-9.6/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/pgsql-9.6/share/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql  '/usr/pgsql-9.6/share/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/pgsql-9.6/doc/extension/'

4. 使用

安裝完成之后，看動(dòng)態(tài)庫(kù)中oracle_fdw.so y有沒有成功生成

4.1 解決使用依賴

ldd 這個(gè)動(dòng)態(tài)庫(kù)文件,，會(huì)顯示缺少哪一些依賴,。要把這些依賴都解決才能使用

我的解決方法

4.2 試用

在PG數(shù)據(jù)庫(kù)中安裝Oracle_fdw插件：

create   extention  oracle_fdw

創(chuàng)建外部數(shù)據(jù)源服務(wù)：

CREATE SERVER server_pgsql_oracle_fdw FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.10.248:1521/orcl');
CREATE USER MAPPING FOR public SERVER server_pgsql_oracle_fdw OPTIONS (user 'kebyy2013', password 'kebyy2013');

創(chuàng)建外部表：

CREATE FOREIGN TABLE dept(deptno smallint options(key 'true'), dname varchar(14), loc varchar(13))
   SERVER oradb OPTIONS (schema 'SCOTT', table 'DEPT');

測(cè)試一下在PG中查詢dept這張表：

osdba=# select * from dept;
 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

可以看出已查詢到數(shù)據(jù)了。

oracle_fdw外部表也可以支持插入,、更新,、刪除:

mydb=> insert into dept values(50, 'OSDBA', 'HANGZHOU');
INSERT 0 1
mydb=> select * from dept;
 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | OSDBA      | HANGZHOU
(5 rows)
mydb=> update dept set loc='HZ' where deptno=50;
UPDATE 1
mydb=> select * from dept;
 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | OSDBA      | HZ
(5 rows)
mydb=> delete from dept where deptno=50;
DELETE 1
mydb=> select * from dept;
 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

5. 附

5.1 oracle的tnsname配置方法

在oracle中通常是使用tnsnames提供的一個(gè)名字訪問(wèn)oracle,，即通過(guò)一個(gè)名字映射到oracle數(shù)據(jù)庫(kù)的IP、端口,、服務(wù)名等等,，
這個(gè)配置通常是配置在文件$ORACLE_HOME/network/admin/tnsnames.ora中，其中$ORACLE_HOME是Oracle中的一個(gè)環(huán)境變量,，安裝過(guò)oracle的人都知道這個(gè)環(huán)境變理,，這個(gè)環(huán)境變量指定了Oracle軟件的安裝目錄，當(dāng)然tnsnames.ora也可以用環(huán)境變量TNS_NAME指定,。我們?cè)谶@個(gè)文件中配置如下內(nèi)容：

oratest =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (sid = oratest)
      (SERVER = DEDICATED)
    )
  )

那邊我們?cè)赑G中建外部數(shù)據(jù)源服務(wù)器的命令中就可以用這個(gè)“oratest”這個(gè)名稱取代IP地址,、端口和服務(wù)名：

CREATE SERVER server_pgsql_oracle_fdw FOREIGN DATA WRAPPER oracle_fdw
          OPTIONS (dbserver 'oratest');