oracle_fdw

測試作業系統:Centos 7.6

測試IP     :192.168.112.88

作業系統安裝資訊:最小化安裝

官方網站:

https://github.com/laurenz/oracle_fdw

http://laurenz.github.io/oracle_fdw/

Requirements:

PostgreSQL 9.3 or better

Oracle client 11.2 or better

For EXPLAIN VERBOSE, SELECT privileges on V$SQL and V$SQL_PLAN

You need to install Oracle's C header files as well (SDK package for Instant Client). 

If you use the Instant Client ZIP files provided by Oracle, 

you will have to create a symbolic link from libclntsh.so to the actual shared library file yourself.

1.準備oracle環境

根據Requirements, fdw 明確要求需要 instanct client 以及  sdk package. 

instantclient-sdk-linux.x64-11.2.0.4.0.zip

instantclient-basic-linux.x64-11.2.0.4.0.zip

instantclient-sqlplus-linux.x64-11.2.0.4.0.zip (可選)

[root@PGHOST u01]# unzip instantclient-basic-linux.x64-11.2.0.4.0.zip

[root@PGHOST u01]# unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip

[root@PGHOST u01]# unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip

檔案均解壓到目錄

/u01/instantclient_11_2

PATH=$PATH:$HOME/bin:/pgsoft/bin:/u01/pgbadger-11.3:/u01/instantclient_11_2

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/pgsoft/lib:/u01/instantclient_11_2

export PATH

2.上傳安裝 fdw

[root@PGHOST u01]# unzip oracle_fdw-master.zip

[root@PGHOST u01]# cd oracle_fdw-master

[root@PGHOST oracle_fdw-master]# make

[root@PGHOST oracle_fdw-master]# make install

3.驗證fdw

[root@PGHOST oracle_fdw-master]# ldd oracle_fdw.so

        linux-vdso.so.1 =>  (0x00007fff084ef000)

        libclntsh.so.11.1 => /u01/instantclient_11_2/libclntsh.so.11.1 (0x00007f812b6c9000)

        libc.so.6 => /lib64/libc.so.6 (0x00007f812b2fc000)

        libnnz11.so => /u01/instantclient_11_2/libnnz11.so (0x00007f812af2f000)

        libdl.so.2 => /lib64/libdl.so.2 (0x00007f812ad2b000)

        libm.so.6 => /lib64/libm.so.6 (0x00007f812aa29000)

        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f812a80d000)

        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f812a5f3000)

        libaio.so.1 => /lib64/libaio.so.1 (0x00007f812a3f1000)

        /lib64/ld-linux-x86-64.so.2 (0x00007f812e25c000)

4.配置oracle_fdw

4.1 基本配置

Then configure oracle_fdw as PostgreSQL superuser like this:

pgdb=# CREATE EXTENSION oracle_fdw;

pgdb=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.112.77:1521/testdb');

pgdb=# GRANT USAGE ON FOREIGN SERVER oradb TO pguser;

Then you can connect to PostgreSQL as pguser and define:

pgdb=> CREATE USER MAPPING FOR pguser SERVER oradb OPTIONS (user 'system', password 'oracle');

4.2 手動建立外部表

pgdb=>  CREATE FOREIGN TABLE TEST (

          id        int,

          text      character varying(30)

       ) SERVER oradb OPTIONS (schema 'SYSTEM', table 'TEST');

postgres=# select * from TEST;

 id  | text 

-----+------

 100 | sss

(1 row)

4.3 使用import FOREIGN SCHEMA 一鍵建立外部表

postgres=# drop user mapping for postgres server oradb;

postgres=# CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'myuser', password 'oracle');

postgres=# create schema r1;

postgres=# import FOREIGN SCHEMA "MYUSER" from server oradb into r1 ;

postgres=# select * from r1.test;

 id  | text 

-----+------

 300 | sss

(1 row)