十二月 19, 2023
摘要：oracle_fdw是一个 PostgreSQL 插件，它提供了一个外部数据包装器，可轻松高效地访问 Oracle 数据库，包括 WHERE 条件和所需列的下推，以及全面的 EXPLAIN 支持。
目录
快速上手
这是一个如何使用 oracle_fdw 的简单示例。
更详细的信息将在“选项”和“用法”部分提供。您还应该阅读有关外部数据的 PostgreSQL 文档，和其中引用的命令。
在本例中，假设您可以使用以下命令，以操作系统用户postgres（或启动 PostgreSQL 服务器的任何用户）进行连接：
sqlplus orauser/orapwd@//dbserver.mydomain.com:1521/ORADB
这意味着 Oracle 客户端和环境已设置正确。我还假设已正确安装 oracle_fdw。
我们想访问一个定义如下的表：
SQL> DESCRIBE oratab
Name Null? Type
------------------------------- -------- ------------
ID NOT NULL NUMBER(5)
TEXT VARCHAR2(30)
FLOATING NOT NULL NUMBER(7,2)
然后将 oracle_fdw 配置为 PostgreSQL 超级用户，如下所示：
pgdb=# CREATE EXTENSION oracle_fdw;
pgdb=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//dbserver.mydomain.com:1521/ORADB');
您可以使用其他命名方法或本地连接，请参阅下面的选项 dbserver 的说明。
只有在真正必要的情况下，才应该去使用超级用户。所以，让我们允许普通用户使用外部服务器（这不是示例所必需的步骤，但是这样做是推荐的）：
pgdb=# GRANT USAGE ON FOREIGN SERVER oradb TO pguser;
然后，您可以用pguser连接到 PostgreSQL，并定义：
pgdb=> CREATE USER MAPPING FOR pguser SERVER oradb
OPTIONS (user 'orauser', password 'orapwd');
您可以使用外部身份验证，来避免存储 Oracle 密码；请参阅下文。
pgdb=> CREATE FOREIGN TABLE oratab (
id integer OPTIONS (key 'true') NOT NULL,
text character varying(30),
floating double precision NOT NULL
) SERVER oradb OPTIONS (schema 'ORAUSER', table 'ORATAB');
请记住，表和模式名称（模式名是可选的）通常必须为大写。
现在，您可以像使用常规的 PostgreSQL 表一样使用该外部表了。
插件创建的对象
FUNCTION oracle_fdw_handler() RETURNS fdw_handler
FUNCTION oracle_fdw_validator(text[], oid) RETURNS void
这些函数是创建一个外部数据包装器所需的注册器和验证器函数。
FOREIGN DATA WRAPPER oracle_fdw
HANDLER oracle_fdw_handler
VALIDATOR oracle_fdw_validator
该插件会自动创建一个名为oracle_fdw的外部数据包装器。通常，这就是您所需要的，您可以继续定义外部服务器。您可以创建其他 Oracle 外部数据包装器，例如，如果您需要设置 nls_lang 选项（您也可以更改现有的oracle_fdw包装器，但在转储/还原后所有修改都会丢失）。
FUNCTION oracle_close_connections() RETURNS void
此函数可用于关闭此会话中所有打开的 Oracle 连接。有关进一步说明，请参阅“用法”部分。
FUNCTION oracle_diag(name DEFAULT NULL) RETURNS text
此函数仅用于诊断目的。它将返回 oracle_fdw、PostgreSQL 服务器和 Oracle 客户端的版本信息。 如果在不带参数或传入 NULL 的情况下调用，它将额外返回一些用于建立 Oracle 连接的环境变量的值。如果使用一个外部服务器的名称来调用，它将额外返回 Oracle 服务器版本信息。
FUNCTION oracle_execute(server name, stmt text) RETURNS void
此函数可用于在远程 Oracle 服务器上执行任意 SQL 语句。这仅适用于不返回结果的语句（通常是 DDL 语句）。
使用该函数时要小心，因为它可能会干扰oracle_fdw的事务管理。请记住，在 Oracle 中运行 DDL 语句将发出隐式 COMMIT。建议您在多语句事务以外使用此函数。
选项
外部数据包装器选项
注意：如果修改默认的外部数据包装器oracle_fdw，则任何更改都将在转储/还原时丢失。如果希望选项持久化，请创建新的外部数据包装器。插件附带的 SQL 脚本中包含您可以使用的 CREATE FOREIGN DATA WRAPPER 语句。
-
nls_lang（可选）
将 Oracle 的 NLS_LANG 环境变量设置为此值。NLS_LANG 的格式为 “language_territory.charset”（例如 AMERICAN_AMERICA.AL32UTF8）。这必须与您的数据库编码匹配。当未设置此值时，如果可能的话，
oracle_fdw会自动选择正确的值，否则会发出警告。仅当您知道自己在做什么时，才应该设置该值。
外部服务器选项
-
dbserver（必需）
远程数据库的 Oracle 数据库连接字符串。这可以是 Oracle 支持的任何形式，只要您的 Oracle 客户端已相应地配置。对于本地（“BEQUEATH”）连接，请将其设置为空字符串。
-
isolation_level（可选，默认为
serializable）要在 Oracle 数据库中使用的事务隔离级别。该值可以是
serializable，read_committed或read_only。请注意，在单个 PostgreSQL 的语句执行期间（例如，在嵌套循环连接期间），可以多次查询 Oracle 表。如要确保不会因并发事务的竞争条件而导致不一致现象的发生，事务隔离级别必须保证读取稳定性。这只能通过 Oracle 的 SERIALIZABLE 或 READ ONLY 隔离级别来保证。
不幸的是，Oracle 的 SERIALIZABLE 实现相当糟糕，并且在某些意外情况下会导致序列化错误（ORA-08177），例如插入数据到表中的时候。使用 READ COMMITTED 事务可以解决此问题，但存在不一致的风险。如果您想使用它，请检查您的执行计划，其中的外部扫描是否可能会多次执行。
-
nchar（布尔型，可选，默认为
off）设置此选项为
on，会在 Oracle 端选择一种更昂贵的字符转换方式。如果您使用的是单字节 Oracle 数据库字符集，则这是必需的。但 NCHAR 或 NVARCHAR2 列包含无法在数据库字符集中表示的字符。设置
nchar为on会带来明显的性能影响，在 UPDATE 语句将字符串设置为超过 2000 字节（如果您设置了 MAX_STRING_SIZE 参数为 EXTENDED，则为 16383）长度时，它会导致 ORA-01461 错误。该错误看起来是一个 Oracle 的 bug。 -
set_timezone（布尔型，可选，默认为
off）设置此选项为
on，可在与 Oracle 建立连接时，将 Oracle 会话时区设置为 PostgreSQL 参数timezone的当前值。仅当您计划使用 TIMESTAMP WITH LOCAL TIME ZONE 类型的 Oracle 列，并希望将它们转换为 PostgreSQL 中的timestamp without time zone时，该设置才是有用的。请注意，如果在建立 Oracle 连接后更改
timezone，oracle_fdw 不会更改 Oracle 会话时区。在这种情况下，您可以调用oracle_close_connections()，以便在下次访问外表时重新建立新连接。如果 Oracle 无法识别时区，连接会失败，并显示如下错误
ORA-01882: timezone region not found在这种情况下，请使用其他的
timezone值，或让选项设置为off，并将环境变量 ORA_SDTZ 设置为 PostgreSQL 服务器环境中的一个适当值。
用户映射选项
-
user（必需）
会话的 Oracle 用户名。如果不想要将 Oracle 访问凭据存储在 PostgreSQL 数据库中，请将其设置为空字符串，以进行外部身份验证。一种简单的方法是使用外部密码存储。
-
password（必需）
Oracle 用户的密码。
外部表选项
-
table（必需）
Oracle 表名称。此名称必须完全按照 Oracle 系统表存储的形式给出，因此通常仅由大写字母组成。
要基于任意 Oracle 查询来定义一个外表，请将选项设置为括在括号中的查询，例如
OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')')在这种情况下，请勿设置 schema 选项。INSERT、UPDATE 和 DELETE 将适用于在简单查询上定义的外部表；如果您想避免这种情况（或者对于更复杂的查询，产生混淆的 Oracle 错误消息），请使用表选项 readonly。
-
dblink（可选）
用于访问表的 Oracle 数据库链接。此名称必须与 Oracle 系统表中的名称完全相同，因此通常仅由大写字母组成。
-
schema（可选）
表的模式（或所有者）。用于访问不属于连接 Oracle 用户的表。此名称必须与 Oracle 系统表中的名称完全相同，因此通常仅由大写字母组成。
-
max_long（可选，默认为“32767”）
Oracle 表中任何 LONG、LONG RAW 和 XMLTYPE 列的最大长度。可能的值是介于 1 和 1073741823（PostgreSQL 中
bytea值的最大长度）之间的整数。该大小的内存至少会分配两次，因此较大的值会消耗大量内存。如果 max_long 小于检索的最长值的长度， 您会接收到错误消息ORA-01406: fetched column value was truncated。 -
readonly（可选，默认为“false”）
只有在此选项未设置为 yes/on/true 的表上，才允许执行 INSERT、UPDATE 和 DELETE。
-
sample_percent（可选，默认为“100”）
此选项仅影响 ANALYZE 的处理，可用于在合理的时间内对非常大的表进行 ANALYZE。
该值必须介于 0.000001 和 100 之间，用于定义将随机选择的 Oracle 表块的百分比，以计算 PostgreSQL 表统计信息。这是使用 Oracle 中的
SAMPLE BLOCK (x)子句完成的。对于使用 Oracle 查询定义的表，ANALYZE 会失败报错 ORA-00933，对于使用复杂 Oracle 视图定义的表，ANALYZE 可能会失败报错 ORA-01446。
-
prefetch（可选，默认为“50”）
该选项用于设置，在外部表扫描期间，在 PostgreSQL 和 Oracle 之间进行单次往返获取的行数。该值必须介于 1 和 1000 之间。
较高的值可以提高性能，但会在 PostgreSQL 服务器上占用更多内存。
请注意，如果 Oracle 表中包含了
MDSYS.SDO_GEOMETRY类型的列，则不会进行预取。 -
lob_prefetch（可选，默认为“1048576”）
设置在读取 BLOB、CLOB 和 BFILE 值时，预取的字节数。超过该大小的 LOB，需要在 PostgreSQL 和 Oracle 之间进行额外的往返交互，因此将此值设置为大于典型 LOB 的大小，将有利于提高性能。为此选项选择较大的值，会在服务器端分配更多内存，但可以提升大型 LOB 值的访问性能。
列选项
-
key（可选，默认为“false”）
如果设置为 yes/on/true，则将外部 Oracle 表上的相应列视为主键列。要使 UPDATE 和 DELETE 正常工作，必须在属于表主键的所有列上设置此选项。
-
strip_zeros（可选，默认为“false”）
如果设置为 yes/on/true，则在传输过程中会从字符串中删除 ASCII 0 字符。此类字符在 Oracle 中是有效的，但在 PostgreSQL 中无效，因此当 oracle_fdw 读取时会导致错误。此选项仅对
character、character varying和text列有意义。
用法
Oracle 权限
Oracle 用户显然需要有 CREATE SESSION 权限，和查询相关表或视图的权限。请注意，oracle_fdw 会在查询规划时访问 Oracle 表以获取其定义信息。这会在检查外表的访问权限之前发生。因此，如果您尝试在 PostgreSQL 中访问一个外部表，所使用的 Oracle 用户并没有该表的访问权限，您可能会接收到 Oracle 的错误消息。这是意料之中的，没有安全问题。
对于 EXPLAIN VERBOSE，用户还需要对视图 V$SQL 和 V$SQL_PLAN 的 SELECT 权限。
连接
oracle_fdw 会缓存 Oracle 连接，因为为每个单独的查询去创建 Oracle 会话的成本很高。当 PostgreSQL 会话结束时，所有连接都会自动关闭。
函数oracle_close_connections()可用于关闭所有缓存的 Oracle 连接。这对于没有一直访问外部表的长时间运行的会话，并希望避免阻塞资源（其他 Oracle 连接需要访问的资源）时非常有用。不能在修改 Oracle 数据的事务中调用此函数。
表列
定义一个外表时，Oracle 表的列将按其定义的顺序映射到 PostgreSQL 列。
oracle_fdw 将在 Oracle 查询中仅包含 PostgreSQL 查询实际需要的列。
PostgreSQL 表的列数可以比 Oracle 表多或少。如果它有更多列，并且使用了这些列，您会接收到警告消息，并将返回 NULL 值。
如果要 UPDATE 或 DELETE，请确保在属于表主键的所有列上设置了key选项。否则会导致错误。
数据类型
您必须使用 oracle_fdw 可以转换的数据类型定义 PostgreSQL 列（请参阅下面的转换表）。仅当实际使用该列时，才会强制执行此限制。 因此，只要您不访问它们，就可以为不可转换的数据类型定义“虚拟”列（此技巧仅适用于 SELECT，不适用于修改外部数据）。如果 Oracle 值超过 PostgreSQL 列的大小（例如，varchar 列的长度或整数列的最大值），您会接收到运行时错误。
这些转化由 oracle_fdw 自动处理：
Oracle type | Possible PostgreSQL types
-------------------------+--------------------------------------------------
CHAR | char, varchar, text
NCHAR | char, varchar, text
VARCHAR | char, varchar, text
VARCHAR2 | char, varchar, text, json
NVARCHAR2 | char, varchar, text
CLOB | char, varchar, text, json
LONG | char, varchar, text
RAW | uuid, bytea
BLOB | bytea
BFILE | bytea (read-only)
LONG RAW | bytea
NUMBER | numeric, float4, float8, char, varchar, text
NUMBER(n,m) with m<=0 | numeric, float4, float8, int2, int4, int8,
| boolean, char, varchar, text
FLOAT | numeric, float4, float8, char, varchar, text
BINARY_FLOAT | numeric, float4, float8, char, varchar, text
BINARY_DOUBLE | numeric, float4, float8, char, varchar, text
DATE | date, timestamp, timestamptz, char, varchar, text
TIMESTAMP | date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH TIME ZONE | date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH | date, timestamp, timestamptz, char, varchar, text
LOCAL TIME ZONE |
INTERVAL YEAR TO MONTH | interval, char, varchar, text
INTERVAL DAY TO SECOND | interval, char, varchar, text
XMLTYPE | xml, char, varchar, text
MDSYS.SDO_GEOMETRY | geometry (see "PostGIS support" below)
如果将 NUMBER 值转换为布尔类型，则 0 表示false，其他所有值都表示true。
要插入或更新 XMLTYPE 值，仅适用于不超过 VARCHAR2 数据类型最大长度的值（4000 或 32767，取决于MAX_STRING_SIZE参数）。
当前还不支持 NCLOB，因为 Oracle 无法自动将其转换为客户端编码。
如果要将 TIMESTAMP WITH LOCAL TIME ZONE 转换为timestamp，请考虑在外部服务器上设置set_timezone选项。
如果需要超出上述的转换，请在 Oracle 或 PostgreSQL 中定义一个适当的视图。
WHERE 条件和 ORDER BY 子句
PostgreSQL 会使用 WHERE 子句的所有适用部分，作为扫描的过滤器。oracle_fdw 构造的 Oracle 查询，会包含与这些筛选条件对应的 WHERE 子句，只要此类条件可以安全地转换成 Oracle 中的 SQL。此功能也称为 WHERE 子句的下推，它可以大大减少从 Oracle 检索到的行数，并能让 Oracle 的优化器选择一个好的计划来访问所需的表。
同样，ORDER BY 子句也会尽可能向下推送到 Oracle。 请注意，不会向下推送按字符串排序的 ORDER BY 条件，因为不能保证 PostgreSQL 和 Oracle 中的字符串排序顺序相同。
要利用好下推能力，请尝试对外部表使用简单的条件。请选择与 Oracle 类型相对应的 PostgreSQL 列数据类型，否则条件无法被转换。
表达式now()、transaction_timestamp()、current_timestamp、current_date和localtimestamp可被正确转换。
EXPLAIN 的输出会显示使用的 Oracle 查询，因此您可以查看哪些条件已转换为 Oracle 的形式，以及如何转换的。
外表之间的连接
从 PostgreSQL 9.6 开始，oracle_fdw 可以将连接向下推送到 Oracle 服务器。也就是说，两个外部表之间的连接，可以转换成在 Oracle 端执行连接的单个 Oracle 查询。
要让连接下推到 Oracle 执行，会存在一些限制：
- 这两个表必须在同一台外部服务器上定义。
- 三个或更多表之间的连接不会被下推。
- 连接必须位于 SELECT 语句中。
- oracle_fdw 必须能够下推所有连接条件和 WHERE 子句。
- 没有连接条件的交叉连接不会被下推。
- 如果下推了一个连接，则不会下推 ORDER BY 子句。
使用 ANALYZE 收集两个外部表的表统计信息，对 PostgreSQL 确定最佳连接策略非常重要。
修改外部数据
oracle_fdw 支持对外表执行 INSERT、UPDATE 和 DELETE 操作。默认情况下，这是允许的（在从 PostgreSQL 早期版本升级过来的数据库中也是如此），并且可以通过设置 readonly 表选项来禁用它。
要使 UPDATE 和 DELETE 正常工作，与 Oracle 表的主键列对应的外表列必须设置 key 列选项。这些列用于标识外表行，因此请确保在属于主键的所有列上设置了该选项。
如果在 INSERT 期间省略外表列，则该列将设置为 PostgreSQL 外表的 DEFAULT 子句中定义的值，如果没有 DEFAULT 子句，则设置为 NULL。Oracle 表列上的 DEFAULT 子句不会使用到。如果 PostgreSQL 外表不包含 Oracle 表的所有列，则 Oracle 表列上的 DEFAULT 子句，将会用于外表定义中未包含的列。
INSERT、UPDATE 和 DELETE 上的 RETURNING 子句是支持的，但具有 Oracle 数据类型 LONG 和 LONG RAW 的列除外（Oracle 在 RETURNING 子句中不支持这些数据类型）。
PostgreSQL 9.4 及以上版本支持外部表上的触发器。使用 AFTER 和 FOR EACH ROW 定义的触发器，要求外表没有 Oracle 数据类型为 LONG 或 LONG RAW 的列。这是因为此类触发器使用了上面提到的 RETURNING 子句。
虽然修改外部数据是有效的，但性能不是特别好，特别是当许多行受到影响时，因为，由于外部数据包装器的工作方式，每一行都必须单独处理。
事务将会转发到 Oracle，因此 BEGIN、COMMIT、ROLLBACK 和 SAVEPOINT 会按预期工作。涉及 Oracle 的预备语句不受支持。
由于 oracle_fdw 默认使用了序列化事务，因此数据修改语句可能会导致序列化失败：
ORA-08177: can't serialize access for this transaction
如果有并发事务同时修改了表，可能会发生这种情况，并且在长时间运行的事务中更有可能发生。此类错误可以通过其 SQLSTATE (40001) 来识别。使用 oracle_fdw 的应用程序应该重试因为该错误而失败的事务。
可以使用不同的事务隔离级别，有关讨论，请参阅外部服务器选项。
EXPLAIN
PostgreSQL 的 EXPLAIN 会显示实际发送给 Oracle 的查询。EXPLAIN VERBOSE 将显示 Oracle 的执行计划（但不适用于 Oracle 服务器 9i 或更早版本）。
ANALYZE
您可以使用 ANALYZE 收集外表的统计信息。这是 oracle_fdw 所支持的。
如果没有统计信息，PostgreSQL 就无法估计外表上查询的行数，这可能会导致选择错误的执行计划。
PostgreSQL 不会像普通表那样使用 autovacuum 守护进程自动收集外表的统计信息，因此在外表创建后以及远程的 Oracle 表发生重大更改时，对外表运行 ANALYZE 尤为重要。
请记住，分析 Oracle 外表会导致完整的顺序表扫描。您可以使用表选项 sample_percent，仅对 Oracle 表进行采样来加快该操作。
PostGIS 支持
数据类型geometry仅在安装了 PostGIS 插件时可用。
只有二维和三维的 POINT、LINE、POLYGON、MULTIPOINT、MULTILINE 和 MULTIPOLYGON，才是受支持的几何类型。空的 PostGIS 几何图形不受支持，因为它们在 Oracle Spatial 中没有等效的几何图形。
Oracle SRID 的 NULL 值将会转换为 0，反之亦然。对于 Oracle SRID 和 PostGIS SRID 之间的其他转换，请在 PostgreSQL 的share目录中创建一个文件srid.map。此文件中的每一行，都应包含一个 Oracle SRID 和相应的 PostGIS SRID，并用空格分隔。请保持文件尽量的小，以获得良好的性能。
对 IMPORT FOREIGN SCHEMA 的支持
从 PostgreSQL 9.5 开始，支持 IMPORT FOREIGN SCHEMA，来批量导入一个 Oracle 模式中所有表的表定义。除了 IMPORT FOREIGN SCHEMA 相关文档中的描述外，还要考虑以下事项：
-
IMPORT FOREIGN SCHEMA 将会为在 ALL_TAB_COLUMNS 中找到的所有对象创建外部表。这包括表、视图和物化视图，但不包括同义词。
-
以下是 IMPORT FOREIGN SCHEMA 支持的选项：
-
case：在导入过程中控制表名和列名的大小写转换
可能的值为：
keep：保留名称在 Oracle 中的样子，通常为大写。lower：将所有表名和列名转换为小写。smart：仅转换 Oracle 中全部大写的名称（这是默认行为）。
-
collation：用于指定 case 选项
lower和smart进行大小写转换时的排序规则默认值为
default，这是数据库的默认排序规则。只有pg_catalog模式中的排序规则才受支持。有关可能值的列表，请参阅pg_collation系统表中的collname值。 -
dblink：用于访问模式的 Oracle 数据库链接
此名称必须与 Oracle 系统表中的名称完全相同。所以通常只由大写字母组成。
-
readonly：在所有导入的表上设置 readonly 选项
有关详细信息，请参阅“选项”部分。
-
max_long：在所有导入的表上设置 max_long 选项
有关详细信息，请参阅“选项”部分。
-
sample_percent：在所有导入的表上设置 sample_percent 选项
有关详细信息，请参阅“选项”部分。
-
prefetch：在所有导入的表上设置 prefetch 选项
有关详细信息，请参阅“选项”部分。
-
lob_prefetch：在所有导入的表上设置 lob_prefetch 选项
有关详细信息，请参阅“选项”部分。
-
nchar：在所有导入的表上设置 nchar 选项
有关详细信息，请参阅“选项”部分。
-
set_timezone：在所有导入的表上设置 set_timezone 选项
有关详细信息，请参阅“选项”部分。
-
-
Oracle 模式名称必须与 Oracle 中的名称完全相同，因此通常为大写。由于 PostgreSQL 在处理之前将名称转换为小写，因此您必须使用双引号保护模式名称（例如
"SCOTT"）。 -
LIMIT TO 或 EXCEPT 子句中的表名，必须编写为它们在大小写转换后出现在 PostgreSQL 中的名称。
请注意，IMPORT FOREIGN SCHEMA 不适用于 Oracle 服务器 8i。