Oracle 12c修改列数据类型报ORA-01439错误求替代方案

ORA-01439报错为Oracle原生限制：修改列数据类型时，若列中已存在数据，无法直接通过ALTER TABLE MODIFY语句完成修改。针对存有业务数据无法清空的场景，可选择以下两种可行方案：

方案1：新增列迁移数据（适合表数据量较小、可接受分钟级业务中断的场景） #

操作步骤如下：

• 新增临时VARCHAR2类型列

ALTER TABLE 你的业务表名 ADD (temp_target_col VARCHAR2(10));

• 同步原NUMBER列数据到临时列，可根据业务需要调整TO_CHAR的格式化参数

UPDATE 你的业务表名 SET temp_target_col = TO_CHAR(原NUMBER列名);
COMMIT;

• 校验临时列数据和原列完全一致后，删除原列

ALTER TABLE 你的业务表名 DROP COLUMN 原NUMBER列名;

• 将临时列重命名为原列名

ALTER TABLE 你的业务表名 RENAME COLUMN temp_target_col TO 原NUMBER列名;

注意事项：

1. 若原列存在索引、约束、注释，重命名完成后需手动重建对应索引、约束，补充列注释；若存在外键关联，需先删除关联约束，操作完成后重建。
2. 若需保证操作过程中数据一致性，可在操作前将表设置为只读模式，操作完成后改回读写模式。
3. 若表数据量极大，UPDATE操作会产生大量REDO日志，耗时较长，该场景下更推荐使用方案2。

方案2：在线重定义（适合大表、需尽量减少业务停机时间的场景） #

Oracle自带的DBMS_REDEFINITION包支持在线重定义表结构，整个过程几乎不影响业务正常读写，操作步骤如下：

• 首先校验表是否支持在线重定义（要求表有主键或唯一非空约束）

-- 替换为实际的用户名、业务表名
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('你的用户名','你的业务表名', DBMS_REDEFINITION.CONS_USE_PK);

• 手动创建临时中间表，表结构除待修改列设为VARCHAR2(10)外，其余列、索引、约束需和原业务表完全一致。
• 启动在线重定义过程

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        => '你的用户名',
    orig_table   => '你的业务表名',
    int_table    => '临时中间表名',
    -- 列映射规则：除待转换列用TO_CHAR处理外，其余列直接映射即可
    col_mapping  => 'TO_CHAR(原NUMBER列名) 原NUMBER列名, 列1 列1, 列2 列2, 列N 列N',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
);
END;
/

• 同步重定义过程中产生的增量数据

EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('你的用户名', '你的业务表名', '临时中间表名');

• 完成重定义，Oracle会自动交换原表和中间表的名称

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('你的用户名', '你的业务表名', '临时中间表名');

• 操作完成后删除临时中间表即可

注意事项：

1. 在线重定义需要占用和原表大小相当的额外存储空间，执行前需确认磁盘空间充足。
2. 建议在业务低峰期执行操作，操作前务必提前备份全量数据。

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