如何批量保留GENERATED AS IDENTITY列原始状态并修改数据类型？

问题背景 #

有数百张含百万级数据的表，每张表都存在通过GENERATED AS IDENTITY创建的唯一自增列REC_ID，数据类型为NUMBER(20,0)。需要将该列数据类型修改为NUMBER(19,0)，且因创建新表成本过高，希望基于现有表改造，同时自动保留IDENTITY列的原始自增状态（无需手动指定START WITH参数）。

原手动方案流程如下：

ALTER TABLE my_schema.my_table 
ADD REC_ID_TEMP NUMBER(19,0);

UPDATE my_schema.my_table 
SET REC_ID_TEMP = REC_ID;

ALTER TABLE my_schema.my_table 
DROP COLUMN REC_ID;

ALTER TABLE my_schema.my_table 
ADD REC_ID NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY;

UPDATE my_schema.my_table 
SET REC_ID = REC_ID_TEMP;

ALTER TABLE my_schema.my_table
MODIFY REC_ID NUMBER(19,0) GENERATED AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999 INCREMENT BY 1 START WITH 1515 -- 需手动设为最后REC_ID值+1
CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE;

但该方案最后一步需要手动查询每张表的最大REC_ID来设置START WITH，无法批量自动化处理。

自动化解决方案（PL/SQL批量脚本） #

通过PL/SQL动态SQL实现批量处理，自动获取每张表的最大REC_ID值，自动生成并执行完整的修改流程：

DECLARE
    v_schema VARCHAR2(30) := 'MY_SCHEMA'; -- 替换为你的目标 schema
    v_max_rec_id NUMBER;
    v_table_name VARCHAR2(30);
    CURSOR c_tables IS
        SELECT table_name
        FROM all_tab_columns
        WHERE owner = v_schema
          AND column_name = 'REC_ID'
          AND data_type = 'NUMBER'
          AND data_precision = 20
          AND data_scale = 0
          AND identity_column = 'YES'; -- 只筛选带IDENTITY属性的REC_ID列
BEGIN
    FOR rec IN c_tables LOOP
        v_table_name := rec.table_name;
        
        -- 1. 添加临时列
        EXECUTE IMMEDIATE 'ALTER TABLE ' || v_schema || '.' || v_table_name || ' ADD REC_ID_TEMP NUMBER(19,0)';
        
        -- 2. 同步数据到临时列（百万级数据建议加并行，或分批更新）
        EXECUTE IMMEDIATE 'UPDATE /*+ PARALLEL(8) */ ' || v_schema || '.' || v_table_name || ' SET REC_ID_TEMP = REC_ID';
        COMMIT;
        
        -- 3. 删除原REC_ID列
        EXECUTE IMMEDIATE 'ALTER TABLE ' || v_schema || '.' || v_table_name || ' DROP COLUMN REC_ID';
        
        -- 4. 新增临时IDENTITY列（GENERATED BY DEFAULT允许手动赋值）
        EXECUTE IMMEDIATE 'ALTER TABLE ' || v_schema || '.' || v_table_name || ' ADD REC_ID NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY';
        
        -- 5. 将临时列数据同步回新REC_ID列
        EXECUTE IMMEDIATE 'UPDATE /*+ PARALLEL(8) */ ' || v_schema || '.' || v_table_name || ' SET REC_ID = REC_ID_TEMP';
        COMMIT;
        
        -- 6. 获取当前最大REC_ID值，计算START WITH值
        EXECUTE IMMEDIATE 'SELECT NVL(MAX(REC_ID), 0) FROM ' || v_schema || '.' || v_table_name INTO v_max_rec_id;
        
        -- 7. 修改REC_ID为GENERATED AS IDENTITY，自动设置START WITH为max+1
        EXECUTE IMMEDIATE 'ALTER TABLE ' || v_schema || '.' || v_table_name || ' MODIFY REC_ID NUMBER(19,0) GENERATED AS IDENTITY ' ||
                          'MINVALUE 1 MAXVALUE 9999999999999999999 INCREMENT BY 1 START WITH ' || (v_max_rec_id + 1) ||
                          ' CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE';
        
        -- 8. 删除临时列
        EXECUTE IMMEDIATE 'ALTER TABLE ' || v_schema || '.' || v_table_name || ' DROP COLUMN REC_ID_TEMP';
        COMMIT;
        
        DBMS_OUTPUT.PUT_LINE('表 ' || v_schema || '.' || v_table_name || ' 处理完成');
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('处理表 ' || v_schema || '.' || v_table_name || ' 出错: ' || SQLERRM);
        ROLLBACK;
END;
/

注意事项 #

• 性能优化：百万级数据的UPDATE语句建议添加并行提示（/*+ PARALLEL(n) */，n根据服务器CPU核数调整），或改为分批更新（避免长时间锁表）。
• 事务控制：每个表的操作独立提交，避免单个表失败导致全部回滚。
• 权限要求：执行脚本的用户需要ALTER TABLE、UPDATE等权限。
• 备份验证：操作前建议先在测试环境验证，或对核心表做备份，确保数据一致性。

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