postgres=# create extension oracle_fdw;
CREATE EXTENSION
postgres=# select * from pg_catalog.pg_available_extensions;
name | default_version | installed_version | comment
------------+-----------------+-------------------+----------------------------------------
plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
oracle_fdw | 1.2 | 1.2 | foreign data wrapper for Oracle access
(2 rows)
■ Oracle client 접속 확인
## Oracle
[oracle@jh-ora admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jh-ora)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@jh-ora admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-MAR-2023 12:58:26
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jh-ora)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 20-MAR-2023 12:57:40
Uptime 0 days 0 hr. 0 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/jh-ora/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jh-ora)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
## tnsname
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.83)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
## PG 서버에서 접속시도
[postgres@jh-post ~]$ sqlplus kim/kim@192.168.100.84:1521/orcl
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 20 12:58:05 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
■ pg_hba.conf 수정(PG)
[postgres@jh-post data]$ vi pg_hba.conf
host all all 192.168.100.0/24 trust
■ Oracle서버에 테스트 데이터 입력(oracle)
SQL> create table test (id number, name varchar2(30), addr varchar2(50));
Table created.
SQL> begin
for i in 1..1000 loop
insert into test values(i||i,'kim'||i,'addr');
end loop;
end;
/
PL/SQL procedure successfully completed.
##
SQL> select * from kim.test where rownum < = 10;
ID NAME ADDR
---------- ------------------------------ --------------------------------------------------
11 kim1 addr
22 kim2 addr
33 kim3 addr
44 kim4 addr
55 kim5 addr
66 kim6 addr
77 kim7 addr
88 kim8 addr
99 kim9 addr
1010 kim10 addr
10 rows selected.
■ Oracle 데이터베이스 연결서버 생성(PG)
postgres=# create server ora11g foreign data wrapper oracle_fdw options(dbserver '//192.168.100.84:1521/orcl');
CREATE SERVER
## dblink 유저 생성
postgres=# create user kim password 'kim' login;
CREATE ROLE
postgres=# grant usage on foreign server ora11g to kim;
GRANT
## oracle 스키마와 매핑
postgres=# create user mapping for kim server ora11g options(user 'kim',password 'kim');
CREATE USER MAPPING
## 데이터베이스 연결 할 유저로 재접속 후 외래 테이블 생성
[postgres@jh-post data]$ psql -U kim
create foreign table fdw_table(
i numeric,
name text,
addr text
)server ora11g options(schema 'KIM', table 'TEST');
CREATE FOREIGN TABLE
## 외래 테이블 조회
postgres=> select * from fdw_table;
i | name | addr
----------+---------+------
11 | kim1 | addr
22 | kim2 | addr
33 | kim3 | addr
44 | kim4 | addr
55 | kim5 | addr
66 | kim6 | addr
77 | kim7 | addr
88 | kim8 | addr
99 | kim9 | addr
1010 | kim10 | addr
1111 | kim11 | addr
1212 | kim12 | addr
1313 | kim13 | addr
1414 | kim14 | addr
1515 | kim15 | addr
1616 | kim16 | addr
1717 | kim17 | addr
1818 | kim18 | addr
1919 | kim19 | addr
2020 | kim20 | addr
2121 | kim21 | addr
2222 | kim22 | addr
2323 | kim23 | addr
2424 | kim24 | addr
2525 | kim25 | addr
2626 | kim26 | addr
...
■ 외래 테이블 조회
select srvname as name,
srvowner::regrole as owner,
fdwname as wrapper,
srvoptions as options
from pg_foreign_server
join pg_foreign_data_wrapper w
on w.oid = srvfdw;
name | owner | wrapper | options
--------+----------+------------+---------------------------------------
ora11g | postgres | oracle_fdw | {dbserver=//192.168.100.84:1521/orcl}
