十二月 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。