时间戳

首页 > 解决方案 > 使用 oracle-fdw 将海量表从 Oracle 导入 PostgreSQL 返回 ORA-01406

oracle - 使用 oracle-fdw 将海量表从 Oracle 导入 PostgreSQL 返回 ORA-01406

问题描述

我从事一个项目，将数据从 Oracle 数据库传输到 PostgreSQL 数据库，以使用 bash 和 SQL 脚本创建数据仓库。为了访问 Oracle 数据库，我使用 PostgreSQL 扩展 oracle-fdw。

我的一个脚本从一个巨大的表中导入数据（~ 100 000 000 新行/天）。该表是分区的，每个分区包含 1 天的数据。我用来导入数据的查询如下所示：

INSERT INTO postgre_target_table (some_fields)
SELECT some_aggregated_fields -- (~150 fields)
FROM oracle_source_table
WHERE partition_id = :v_partition_id AND some_others_filters
GROUP BY primary_key;

在 DEV 服务器上，查询工作正常（此服务器上的数据少得多），但在 PREPROD 中，它返回错误ORA-01406: fetched column value was truncated。

在一些帖子中，人们说输出字段可能太小，但如果我尝试发送一个没有 INSERT 或 GROUP BY 的简单 SELECT 查询，我会遇到同样的错误。

我在另一篇文章中发现的另一个想法是创建一个 Oracle 侧视图，但在我的查询中，我使用了多个无法在视图中使用的参数。

我发现的最后一个想法是创建一个 Oracle 存储过程，用聚合数据填充表，然后从该表中导入数据，但 Oracle 数据库很关键，我的客户更喜欢避免在其上添加更多数据。

现在，我开始认为没有解决方案，而且不好......

PostgreSQL 版本：12.4 / Oracle 版本：11.2

更新

看来我的问题比我想象的要复杂。

应用 Laurenz Albe 给出的修改后，查询在 PGAdmin 上正确运行，但当我使用psql命令时问题仍然出现。此外，另一个查询似乎也有同样的问题。这个另一个查询不使用与第一个查询相同的源表，它使用 4 个没有任何分区的连接表。这些查询之间的共同点是结构。

我在原始帖子中省略指定的细节是这两个查询的目的是透视表。他们看起来像这样：

SELECT osr.id,
   MIN(CASE osr.category
      WHEN 123 THEN
         1
      END) AS field1,
   MIN(CASE osr.category
      WHEN 264 THEN
         1
      END) AS field2,
   MIN(CASE osr.category
      WHEN 975 THEN
         1
      END) AS field3,
...
FROM oracle_source_table osr
WHERE osr.category IN (123, 264, 975, ...)
GROUP BY osr.id;

现在我已经详细说明了查询的样子，我可以在不更改 max_long 的值的情况下为您提供第二个查询的一些结果（此查询比第一个查询轻）：

  1. 有时它可以工作（~10%），有时它在 PGadmin 上失败（~90%），但它从不使用psql命令
  2. 如果我删除WHERE，它总是有效的

我不明白为什么要删除WHERE更改的内容，此子句中使用的字段是介于 0 和 2500 之间的NUMBER(6, 0)并且仍在SELECT子句中使用...哦，在此使用的 4 个 Oracle 表中查询，没有LONG数据类型，只使用NUMBER数据类型。

在我的 20 个查询中，只有这两个有问题，它们的结构相似，我不相信巧合。

标签： oraclepostgresqloracle-fdw

解决方案

不要绝望！

将max_long外部表上的选项设置得足够大，以适应所有超大数据。

该文档有详细信息：

  • max_long（可选，默认为“32767”）

Oracle 表中任何 LONG、LONG RAW 和 XMLTYPE 列的最大长度。可能的值是 1 到 1073741823 之间的整数（PostgreSQL 中一个字节的最大大小）。此内存量将至少分配两次，因此较大的值将消耗大量内存。

如果max_long小于检索到的最长值的长度，您将收到错误消息

ORA-01406: fetched column value was truncated

例子：

ALTER FOREIGN TABLE my_tab OPTIONS (ADD max_long '1000000');

推荐阅读