Oracle 11G执行DROP COLUMN后其他列数据异常的原因咨询

这绝对是Oracle 11g版本里一个坑人的已知bug！我之前也碰到过类似的情况，折腾了好一阵才搞清楚根源，也找到了靠谱的应对方法。

问题根源 #

在Oracle 11g及更早版本中，表的列默认值存储在数据字典表COL$里，而且Oracle内部是通过**列的创建顺序（内部COL#编号）**来关联默认值，而非列名。当你执行ALTER TABLE DROP COLUMN删除某一列后，后续所有列的内部COL#会向前移位，直接导致默认值的关联关系彻底混乱——原本属于被删除列的默认值（或空的默认值定义）会被错误绑定到下一列，覆盖该列原有的默认值，甚至强制修改现有数据的值。

对应你的操作场景：

1. 你分四次添加四列，每列都有独立的COL#（假设原有列到COL#=4，新列依次是COL#=5、6、7、8）。
2. 删除第一列后，第二列的COL#变成5，Oracle错误地把原本第一列的默认值设置（或空定义）绑定到第二列，导致第二列丢失了default 0的设置，甚至因为NOT NULL约束的冲突，把现有数据改成了NULL（这明显违反约束，但bug就是这么离谱）。
3. 当你删除第二列后，第三列的COL#变成5，又被错误绑定了原本第一列或第二列的默认值（0），所以原本默认值为1的第三列，所有数据都被改成了0。

这个bug在Oracle 12c及以后版本已经被完全修复，新版本改用列名关联默认值，不会再出现这种移位错乱的问题。

解决办法 #

1. 优先升级数据库版本

如果条件允许，直接升级到Oracle 12c+，一劳永逸解决这个问题。

2. 避免分多次添加列

如果还停留在11g，以后添加多列时，一定要用单个ALTER TABLE语句一次性添加所有列，不要分多次执行ALTER TABLE ADD。比如把你的列添加语句改成：

alter table my_table add (
    pin_validacao_cadastro varchar2(6 char) default '000000' not null,
    tentativas_validacao_pin number default 0 not null,
    codigo_bloqueio number default 1 not null check (codigo_bloqueio in (0, 1, 2)),
    data_validacao_cadastro date
);

这种方式添加的列，内部默认值关联逻辑绑定到列名，不会因为后续删除列出现错乱。

3. 修复当前已出现的异常

现在你的表已经出现数据异常，建议按以下步骤修复：

• 先备份当前表的所有数据（比如用CREATE TABLE my_table_backup AS SELECT * FROM my_table;）
• 删除有问题的codigo_bloqueio列，再用单个ALTER语句重新添加该列和之前删除的两列（按需调整）
• 从备份表中恢复原有数据（注意避免新列默认值和备份数据的冲突）

另外要提醒：Oracle 11g中删除列是较重的操作，Oracle会重建整个表，执行前一定要选业务低峰期，避免影响性能。

内容的提问来源于stack exchange，提问作者Carlos N.