Oracle非UTF8字符排查修复方案（适配PostgreSQL迁移）

一、批量排查问题表与列 #

针对400+大表的场景，单列排查效率极低，以下两种批量方案可快速定位问题：

1. 自动生成全量检查SQL #

先查询目标schema下所有字符型列（VARCHAR2/CHAR/CLOB），批量生成检查语句，执行后直接得到问题表、列及无效行数：

SELECT 'SELECT ''' || table_name || ''' AS table_name, ''' || column_name || ''' AS column_name, COUNT(*) AS invalid_rows_count FROM ' || owner || '.' || table_name || ' WHERE INSTR(RAWTOHEX(utl_raw.cast_to_raw(utl_i18n.raw_to_char(utl_raw.cast_to_raw(' || column_name || '), ''UTF8''))), ''EFBFBD'') > 0;' AS check_sql
FROM all_tab_columns
WHERE owner = '你的Schema名称' -- 替换为实际schema
  AND data_type IN ('VARCHAR2', 'CHAR', 'CLOB')
ORDER BY table_name, column_name;

将生成的所有SQL批量执行，即可汇总所有存在无效字符的表和列。

2. 高效扫描PL/SQL脚本 #

如果表数据量超大，逐行返回结果太慢，可使用PL/SQL块仅记录问题表列，减少输出开销：

SET SERVEROUTPUT ON;
DECLARE
  v_count NUMBER;
  v_sql VARCHAR2(2000);
BEGIN
  FOR rec IN (SELECT owner, table_name, column_name
              FROM all_tab_columns
              WHERE owner = '你的Schema名称'
                AND data_type IN ('VARCHAR2', 'CHAR', 'CLOB')) LOOP
    v_sql := 'SELECT COUNT(*) FROM ' || rec.owner || '.' || rec.table_name || 
             ' WHERE INSTR(RAWTOHEX(utl_raw.cast_to_raw(utl_i18n.raw_to_char(utl_raw.cast_to_raw(' || rec.column_name || '), ''UTF8''))), ''EFBFBD'') > 0';
    EXECUTE IMMEDIATE v_sql INTO v_count;
    IF v_count > 0 THEN
      DBMS_OUTPUT.PUT_LINE('表: ' || rec.table_name || ', 列: ' || rec.column_name || ', 无效行数: ' || v_count);
    END IF;
  END LOOP;
END;
/

执行后直接输出所有存在问题的表和列信息。

二、数据修复方案 #

定位问题后，针对不同类型的列执行修复，大表需分批处理避免锁表和性能问题：

1. VARCHAR2/CHAR列修复 #

将无效字符替换为空字符串（也可换成空格或其他合法字符），大表分批更新：

DECLARE
  v_batch_size NUMBER := 10000; -- 每次更新1万行，可根据服务器性能调整
  v_rows_updated NUMBER := 1;
BEGIN
  WHILE v_rows_updated > 0 LOOP
    UPDATE 你的Schema.目标表
    SET 目标列 = REPLACE(utl_i18n.raw_to_char(utl_raw.cast_to_raw(目标列), 'UTF8', 'REPLACE'), '�', '')
    WHERE INSTR(RAWTOHEX(utl_raw.cast_to_raw(utl_i18n.raw_to_char(utl_raw.cast_to_raw(目标列), 'UTF8'))), 'EFBFBD') > 0
      AND ROWNUM <= v_batch_size;
    v_rows_updated := SQL%ROWCOUNT;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('本次更新 ' || v_rows_updated || ' 行');
  END LOOP;
END;
/

2. CLOB列修复 #

CLOB列修复逻辑类似，直接替换无效字符即可，同样建议分批处理：

DECLARE
  v_batch_size NUMBER := 5000;
  v_rows_updated NUMBER := 1;
BEGIN
  WHILE v_rows_updated > 0 LOOP
    UPDATE 你的Schema.目标表
    SET CLOB列 = REPLACE(utl_i18n.raw_to_char(utl_raw.cast_to_raw(CLOB列), 'UTF8', 'REPLACE'), '�', '')
    WHERE INSTR(RAWTOHEX(utl_raw.cast_to_raw(utl_i18n.raw_to_char(utl_raw.cast_to_raw(CLOB列), 'UTF8'))), 'EFBFBD') > 0
      AND ROWNUM <= v_batch_size;
    v_rows_updated := SQL%ROWCOUNT;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('本次更新 ' || v_rows_updated || ' 行');
  END LOOP;
END;
/

3. 预防后续无效字符 #

• 修改应用程序，确保写入数据库的所有字符均为UTF8编码
• 可在数据库列上添加约束，或调整NLS参数强化字符集验证（需结合业务环境评估）

三、修复验证 #

修复完成后，再次执行批量排查脚本，确认所有表列的无效行数为0，随后用ora2pg导出数据，验证是否能正常导入PostgreSQL。

内容的提问来源于stack exchange，提问作者Amit