为什么这个 Oracle DROP COLUMN 会改变另一列的默认值?
我们在 Oracle 数据库中的表上遇到了一个奇怪的情况，删除一列会导致更改另一列的默认值.这是场景.
We have a weird situation on a table in an Oracle database where dropping a column results in changing the default values of another column. Here's the scenario.
我的表中有一些示例数据:
I have my table with some sample data in it :
select * from SAMPLE_TABLE ;
ID BUSINESS_KEY
---------------------------------------- ---------------
e59bf31c-49a4-4638-bf6e-7d1be60f4dbb 1
c0dabf78-d9ca-4072-832e-aeb618c7ed14 2
我添加带有检查约束(TYPE1_VAL1 或 TYPE1_VAL2)和默认值 TYPE1_VAL2 的列 TYPE1 :
I add column TYPE1 with a check constraint (TYPE1_VAL1 or TYPE1_VAL2) and a default value TYPE1_VAL2 :
alter table SAMPLE_TABLE add TYPE1 varchar(10) default 'TYPE1_VAL2' not null check(TYPE1 in ('TYPE1_VAL1', 'TYPE1_VAL2'));
Table altered.
我看到默认值 (TYPE1_VAL2) 已正确填写:
I see that the default value (TYPE1_VAL2) is correctly filled in:
select * from SAMPLE_TABLE ;
ID BUSINESS_KEY TYPE1
---------------------------------------- --------------- ----------
e59bf31c-49a4-4638-bf6e-7d1be60f4dbb 1 TYPE1_VAL2
c0dabf78-d9ca-4072-832e-aeb618c7ed14 2 TYPE1_VAL2
我使用另一个检查约束(TYPE2_VAL1 或 TYPE2_VAL2)和默认值 TYPE2_VAL2 添加另一列 TYPE2:
I add another column TYPE2 with another check constraint (TYPE2_VAL1 or TYPE2_VAL2) and a default value TYPE2_VAL2 :
alter table SAMPLE_TABLE add TYPE2 varchar(15) default 'TYPE2_VAL2' not null check(TYPE2 in ('TYPE2_VAL1', 'TYPE2_VAL2'));
Table altered.
再次看到默认值 (TYPE2_VAL2) 是正确的:
And again see that the default value (TYPE2_VAL2) is correct :
SYSTEM(SYSTEM) @ DB_USER > select * from SAMPLE_TABLE ;
ID BUSINESS_KEY TYPE1 TYPE2
---------------------------------------- --------------- ---------- ---------------
e59bf31c-49a4-4638-bf6e-7d1be60f4dbb 1 TYPE1_VAL2 TYPE2_VAL2
c0dabf78-d9ca-4072-832e-aeb618c7ed14 2 TYPE1_VAL2 TYPE2_VAL2
现在是奇怪的部分.当我删除第一列时，它似乎将删除列中的默认值应用到剩余的列上:
And now for the weird part. When I drop the first column, it seems to apply the default value from the dropped column onto the remaining column:
ALTER TABLE SAMPLE_TABLE DROP COLUMN TYPE1;
Table altered.
select * from SAMPLE_TABLE ;
ID BUSINESS_KEY TYPE2
---------------------------------------- --------------- ---------------
e59bf31c-49a4-4638-bf6e-7d1be60f4dbb 1 TYPE1_VAL2
c0dabf78-d9ca-4072-832e-aeb618c7ed14 2 TYPE1_VAL2
所以在 TYPE2 列之前包含 TYPE2_VAL2 的地方，在删除之后突然包含 TYPE1_VAL2.就好像删除列的检查约束移动到了这一列.
So where before the TYPE2 column contained TYPE2_VAL2, all of a sudden after the drop it contains TYPE1_VAL2. It's as if the check constraint of the dropped column moved to this column.
这发生在我们在 Linux 上运行 Oracle Database 11g 版本 11.2.0.4.0 - 64 位生产环境的测试环境中.
This happens on our test environment where we are running Oracle Database 11g Release 11.2.0.4.0 - 64bit Production on Linux.
在我们本地的 CentOS/Oracle XE 版本上，我们没有这个问题.
On our local CentOS / Oracle XE edition we don't have this issue.
知道是什么导致了这种情况以及我们如何防止这种情况发生.这是设计使然/错误/我们的错误吗?
Any idea what could cause this and how we can prevent this from happening. Is this by design / a bug / a mistake on our part ?
推荐答案
这是一个 Oracle 错误.
This is an Oracle bug.
它是通过向现有表添加具有 NOT NULL 约束和 DEFAULT 值的列来触发的.
It is triggered by adding a column with both a NOT NULL constraint and a DEFAULT value to an existing table.
为了快速添加列，Oracle 11g 将默认值存储在数据字典中.Oracle 将此称为添加列优化".
To add the column quickly, Oracle 11g stores the default value in the data dictionary. Oracle calls this "add column optimization".
这比将默认值写入每个表行要快.然后查询引擎应该用数据字典中的默认值替换表行中的任何 NULL.不幸的是，有几个与此相关的错误.你的似乎是一个实例:
This is faster than writing out the default value into every table row. The query engine is then supposed to replace any NULL in the table row with the default value from the data dictionary. Unfortunately there are several bugs related to this. Yours appears to be an instance of:
17325413 删除具有 DEFAULT 值和 NOT NULL 定义的列结束删除的列数据击中磁盘导致损坏
17325413 Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption
您可以像这样检查添加了哪些列:
You can check which columns have been added like this:
select owner, object_name, name
from dba_objects, col$
where bitand(col$.PROPERTY,1073741824)=1073741824
and object_id=obj#;
在我们的例子中，我们被一个不同的错误刺痛了，它为 SELECT FOR UPDATE 返回了错误的结果.
In our case, we were stung by a different bug which returned the incorrect results for a SELECT FOR UPDATE.
我们设置参数 _add_col_optim_enabled=FALSE 来关闭这个优化".或者，您可以升级到已解决这些错误的更高版本的 Oracle.
We set parameter _add_col_optim_enabled=FALSE to turn off this "optimisation". Alternatively, you may be able to upgrade to a later Oracle version where these bugs are resolved.
升级或设置上述参数不会修复您现有的表，该表已损坏.您必须重新创建该表.
Upgrading or setting the above parameter will not fix your existing table, which is corrupt. You must re-create that table.
相关文章