oracle_fdw简介
oracle_fdw是PostgreSQL外部表插件，可以读取Oracle数据库的数据，可以使PostgreSQL轻松跨库操作Oracle。
oracle_fdw使用主要有以下三个步骤：
1，安装和创建对应插件(create extension)2，创建远程数据库链接(create server)，指定对端数据库的物理信息3，创建用户映射关系(create user mapping)，指定对端数据库的认证信息
加载扩展
postgres=# CREATE EXTENSION oracle_fdw;
postgres=# \dx
已安装扩展列表
名称 | 版本 | 架构模式 | 描述
--------------------+------+------------+------------------------------------------------------------------------
adminpack | 2.1 | pg_catalog | administrative functions for PostgreSQL
dblink | 1.2 | postgres | connect to other PostgreSQL databases from within a database
oracle_fdw | 1.2 | postgres | foreign data wrapper for Oracle access
pg_stat_statements | 1.9 | postgres | track planning and execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(5 行记录)
--查看扩展版本
postgres=# SELECT oracle_diag();
oracle_diag
-------------------------------------------------------------
oracle_fdw 2.4.0, PostgreSQL 14.4, Oracle client 11.2.0.4.0
(1 行记录)
授权用户访问权限
psql -U postgres hrdb
postgres=# grant USAGE on FOREIGN data wrapper oracle_fdw to hr;
GRANT
创建server对象
--创建server语法：
CREATE SERVER <SERVER名称>
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//<Oracle数据库的内网连接地址>:<Oracle数据库的内网连接端口>/<数据库名>');
--普通用户hr操作创建server
psql -U hr hrdb
hrdb=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//10.10.10.1:1521/orcl');
CREATE SERVER
--查看server对象
hrdb=> SELECT * FROM pg_foreign_server;
oid | srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions
-------+---------+----------+--------+---------+------------+--------+-----------------------------------
74245 | oradb | 16446 | 74244 | | | | {dbserver=//10.10.10.1:1521/orcl}
(1 行记录)
或
SELECT s.srvname, w.fdwname, s.srvoptions
FROM pg_foreign_server s
JOIN pg_foreign_data_wrapper w ON w.oid = s.srvfdw;
srvname | fdwname | srvoptions
---------+------------+--------------------------------
oradb | oracle_fdw | {dbserver=127.0.0.1:1521/orcl}
(1 行记录)
--删除server
DROP SERVER IF EXISTS oradb CASCADE;
创建映射
--创建到oracle的映射
create user mapping for hr server oradb options(user 'SCOTT',password 'tiger');
grant usage on foreign server oradb to hr;
--查看映射
postgres=# select * from pg_user_mapping;
oid | umuser | umserver | umoptions
-------+--------+----------+-----------------------------
74229 | 16446 | 74228 | {user=SCOTT,password=tiger}
创建外部表
创建需要访问Oracle的对应表，这里创建的时候要注意字段类型的转换，Oracle和PG库在字段类型上还是有所差别的。
create foreign table emptest(
EMPNO integer not null,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR integer,
HIREDATE DATE,
SAL NUMERIC(7,2),
COMM NUMERIC(7,2),
DEPTNO integer
) server oradb options(schema 'SCOTT',table 'EMP')
;
create foreign table emptest1(
EMPNO integer not null,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR integer,
HIREDATE DATE,
SAL NUMERIC(7,2),
COMM NUMERIC(7,2),
DEPTNO integer
) server oradb options(schema 'SCOTT',
table 'EMP',
max_long '32767',
readonly 'false',
sample_percent '100',
prefetch '200');
注意：
1、外部表的结构需要和Oracle中的映射表结构保持一致。
2、oracle 端表字段发生变化，需要重建外部表。
OPTIONS内的参数说明如下
key：是否设置对应的列为主键，取值为true或false，默认值为false。如果要执行UPDATE和DELETE操作，必须将所有主键列设置为true。 table 表名，一般是大写，必填参数。可以使用Oracle的SQL来定义table变量的值，例如：OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')')，此时不要使用schema参数。
schema：一般是Oracle用户名，保持大写，用来访问不属于当前连接用户的表。
max_long：限制Oracle表中LONG、LONG RAW、XMLTYPE类型列的最大长度，取值范围是1~1073741823，默认值是32767。
readonly：限制Oracle表为只读，不允许INSERT、UPDATE、DELETE操作。
sample_percent：设置随机选择Oracle表数据的比例，用于PostgreSQL表统计信息，取值范围是0.000001~100，默认值是100。
prefetch：外表扫描时，PostgreSQL和Oracle数据表之间一次性传输的行数，取值范围是0~1024，默认值是200，0代表取消prefetch功能。
访问外部表
通过外部表查询oracle数据库t2表数据
hrdb=> select * from emptest;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10
7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10
8558 | szrsu | DBA | 7782 | 2024-03-12 | 2500.00 | 0.00 | 10
(13 行记录)
dml操作外部表
hrdb=> insert into emptest values (8668,'aaa','ceshi',7782,'2025-4-18',2500,0,10);
INSERT 0 1
hrdb=> select * from emptest;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10
7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10
8558 | szrsu | DBA | 7782 | 2024-03-12 | 2500.00 | 0.00 | 10
8668 | aaa | ceshi | 7782 | 2025-04-18 | 2500.00 | 0.00 | 10
(14 行记录)
hrdb=> delete from emptest where empno=8668;
错误: no primary key column specified for foreign Oracle table
描述: For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.
提示: Set the option "key" on the columns that belong to the primary key.
hrdb=> ALTER FOREIGN TABLE emptest
hrdb-> ALTER COLUMN empno OPTIONS (ADD key 'true');
ALTER FOREIGN TABLE
hrdb=> delete from emptest where empno=8668;
hrdb=> update emptest set ename='ceshi' where empno=8558;
UPDATE 1
hrdb=> select * from emptest;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10
7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10
8558 | ceshi | DBA | 7782 | 2024-03-12 | 2500.00 | 0.00 | 10
(13 行记录)
一键导入外部表
IMPORT FOREIGN SCHEMA will create foreign tables for all objects found in ALL_TAB_COLUMNS. That includes tables, views and materialized views, but not synonyms.
支持一键导入所有表、视图和物化视图
--导入语法：
IMPORT FOREIGN SCHEMA <ora_schema_name>
FROM SERVER <server_name>
INTO <schema_name>
OPTIONS (case 'lower');
--SCOTT用户的下的所有表结构导入到postgres下
IMPORT FOREIGN SCHEMA "SCOTT"
FROM SERVER oradb
INTO hr
OPTIONS (case 'lower');
###主要postgre相关用户下不要存在同名的表，否则会提示表已经存在，导入失败。
--导入后查看
hrdb=> \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+------------------------------+--------------+--------
hr | a | 所引用的外表 | hr
hr | apple | 所引用的外表 | hr
hr | b | 所引用的外表 | hr
hr | big_emp | 所引用的外表 | hr
hr | bonus | 所引用的外表 | hr
hr | bonuses | 所引用的外表 | hr
hr | coursemaster | 所引用的外表 | hr
hr | demotable | 数据表 | hr
hr | department | 数据表 | hr
hr | department_employee | 数据表 | hr
hr | department_manager | 数据表 | hr
hr | dept | 所引用的外表 | hr
hr | dept2 | 所引用的外表 | hr
hr | emp | 所引用的外表 | hr
hr | emp2 | 所引用的外表 | hr
hr | emp@orcl | 所引用的外表 | hr
hr | emp_bonus | 所引用的外表 | hr
hr | employee | 数据表 | hr
hr | emptest | 所引用的外表 | hr
hr | emptest1 | 所引用的外表 | hr
hr | fk_t | 所引用的外表 | hr
hr | greatab | 所引用的外表 | hr
hr | h_test | 所引用的外表 | hr
hr | hao1 | 所引用的外表 | hr
hr | hao2 | 所引用的外表 | hr
hr | hao3 | 所引用的外表 | hr
hr | hao4 | 所引用的外表 | hr
oracle_fdw使用效率
oracle_fdw是支持谓词下推。
看下单表查询情况：
hrdb=> EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM hr.emptest WHERE empno = 8558;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on hr.emptest (cost=10000.00..20000.00 rows=1000 width=118) (actual time=0.363..0.371 rows=1 loops=1)
Output: empno, ename, job, mgr, hiredate, sal, comm, deptno
Oracle query: SELECT /*ab6b3416ada57caf2c6e342c08a46344*/ r1."EMPNO", r1."ENAME", r1."JOB", r1."MGR", r1."HIREDATE", r1."SAL", r1."COMM", r1."DEPTNO" FROM "SCOTT"."EMP" r1 WHERE (r1."EMPNO" = 8558)
Oracle plan: SELECT STATEMENT
Oracle plan: TABLE ACCESS BY INDEX ROWID EMP
Oracle plan: INDEX UNIQUE SCAN PK_EMP (condition "R1"."EMPNO"=8558)
Query Identifier: -977648984178158970
Planning Time: 0.862 ms
Execution Time: 0.393 ms
(9 行记录)
Oracle query: 发送到 Oracle 的实际 SQL 包含WHERE (r1."EMPNO" = 8558)，表明过滤条件已下推。Oracle plan: Oracle 使用索引PK_EMP进行唯一扫描（INDEX UNIQUE SCAN），直接定位到符合条件的行，无需全表扫描。
两表关联情况：
hrdb=> EXPLAIN (ANALYZE, VERBOSE) SELECT a.* FROM hr.emptest a,hr.emptest1 b WHERE a.empno=b.empno and a.empno = 8558;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=20000.00..52502.50 rows=1000000 width=118) (actual time=0.477..0.492 rows=1 loops=1)
Output: a.empno, a.ename, a.job, a.mgr, a.hiredate, a.sal, a.comm, a.deptno
-> Foreign Scan on hr.emptest a (cost=10000.00..20000.00 rows=1000 width=118) (actual time=0.281..0.284 rows=1 loops=1)
Output: a.empno, a.ename, a.job, a.mgr, a.hiredate, a.sal, a.comm, a.deptno
Oracle query: SELECT /*ab6b3416ada57caf2c6e342c08a46344*/ r1."EMPNO", r1."ENAME", r1."JOB", r1."MGR", r1."HIREDATE", r1."SAL", r1."COMM", r1."DEPTNO" FROM "SCOTT"."EMP" r1 WHERE (r1."EMPNO" = 8558)
Oracle plan: SELECT STATEMENT
Oracle plan: TABLE ACCESS BY INDEX ROWID EMP
Oracle plan: INDEX UNIQUE SCAN PK_EMP (condition "R1"."EMPNO"=8558)
-> Materialize (cost=10000.00..20005.00 rows=1000 width=4) (actual time=0.194..0.204 rows=1 loops=1)
Output: b.empno
-> Foreign Scan on hr.emptest1 b (cost=10000.00..20000.00 rows=1000 width=4) (actual time=0.191..0.201 rows=1 loops=1)
Output: b.empno
Oracle query: SELECT /*ce99b44a36eda5bed5dcd62a15b9f0d5*/ r2."EMPNO" FROM "SCOTT"."EMP" r2 WHERE (r2."EMPNO" = 8558)
Oracle plan: SELECT STATEMENT
Oracle plan: INDEX UNIQUE SCAN PK_EMP (condition "R2"."EMPNO"=8558)
Query Identifier: -2990080726012200983
Planning Time: 1.111 ms
Execution Time: 0.528 ms
(18 行记录)
从提供的执行计划来看，查询的谓词条件已成功下推到 Oracle 数据库，且整体性能极优。
其他操作
-- 查询已创建的到oracle的连接
SELECT * from pg_user_mappings;
-- 删除创建的对象
drop foreign table emptest;
drop user mapping for hr server oradb;
drop server oradb;