Oracle重命名Schema后Identity列序列异常，如何无需重建表修复？

问题原因 #

将Oracle Schema（用户）从ACTIVATION_MS重命名为ACTIVATION_MS_DEV后，系统生成的IDENTITY列序列的归属者已自动更新为新Schema，但表列的默认值定义仍保留旧Schema前缀，导致插入数据时触发"sequence not found"错误。

解决方案（无需重建表） #

1. 定位目标列与对应序列 #

先查询所有带IDENTITY属性且默认值引用旧Schema的列，以及对应的序列信息：

SELECT 
    col.table_name,
    col.column_name,
    seq.sequence_name,
    seq.sequence_owner
FROM user_tab_columns col
JOIN user_sequences seq
    ON col.data_default LIKE '%' || seq.sequence_name || '%'
WHERE 
    col.identity_column = 'YES'
    AND col.data_default LIKE '"ACTIVATION_MS".%'; -- 筛选引用旧Schema的列

该语句会返回所有需要修复的表、列名，以及对应序列的名称和当前归属的新Schema。

2. 单个表的修复语句 #

针对查询结果中的每个表和列，执行ALTER TABLE语句修改列的默认值，替换旧Schema前缀：

ALTER TABLE YOUR_TABLE_NAME MODIFY (
    ID NUMBER(38) DEFAULT "ACTIVATION_MS_DEV"."ISEQ$$_XXXXXX".NEXTVAL GENERATED ALWAYS AS IDENTITY
);

• 替换YOUR_TABLE_NAME为实际表名
• 替换ISEQ$$_XXXXXX为查询到的对应序列名称
• 若当前会话默认Schema为ACTIVATION_MS_DEV，可省略Schema前缀，直接写"ISEQ$$_XXXXXX".NEXTVAL
• 注意保持GENERATED ALWAYS与原列定义一致（原列若为GENERATED BY DEFAULT，则替换为该关键字）

3. 批量修复（多表场景） #

若有大量表需要修复，可通过以下语句自动生成所有修复用的ALTER TABLE脚本：

SELECT 
    'ALTER TABLE ' || table_name || ' MODIFY (' || column_name || ' NUMBER(38) DEFAULT "' || sequence_owner || '"."' || sequence_name || '".NEXTVAL GENERATED ALWAYS AS IDENTITY);' AS repair_sql
FROM (
    SELECT 
        col.table_name,
        col.column_name,
        seq.sequence_name,
        seq.sequence_owner
    FROM user_tab_columns col
    JOIN user_sequences seq
        ON col.data_default LIKE '%' || seq.sequence_name || '%'
    WHERE 
        col.identity_column = 'YES'
        AND col.data_default LIKE '"ACTIVATION_MS".%'
);

执行后复制生成的repair_sql内容，批量执行即可完成所有表的修复。

注意事项 #

• 操作前建议在测试环境验证，避免影响生产数据
• 确保执行语句的用户拥有ALTER TABLE权限
• 修改后可通过DBMS_METADATA.GET_DDL('TABLE', 'YOUR_TABLE_NAME')查看表DDL，确认默认值已更新为新Schema的序列引用

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