Oracle数据库添加列失败？原来是因为这个！

说明：

本文整理自我的ITPUB 2018年的一篇博客，原文链接如下：

https://blog.itpub.net/29785807/viewspace-2153428/

问题现象:

国产某数据库同步软件数据同步失败，其中源端是Oracle dg的备库，主库添加字段后，正常同步到dg备库，但dg备库通过国产同步软件同步到目标端数据库时同步中断。

ORA-39726:unsupported add/drop column operation on compressed tables

问题原因：

客户为了节约空间，在目标端启用了压缩表空间，压缩级别Basic compression，在Basic compression下，增加字段时不能指定default，否则报错ORA-39726；看来使用不熟悉的功能不能只看好的一面，还要充分评估有哪些限制。

https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11630

问题重现：

SQL> create table t1(id number(10) not null) compress;
SQL> insert into t1 values(1);
SQL> commit;
SQL> ALTER TABLE t1 ADD UPDATE_TIME DATE DEFAULT SYSDATE NOT NULL ;
ALTER TABLE t1 ADD UPDATE_TIME DATE DEFAULT SYSDATE NOT NULL
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

解决方案：

SQL> SELECT table_name, compression, compress_for FROM user_tables where table_name='T1';
TABLE_NAME COMPRESS COMPRESS_FOR
---------- -------- ------------------------------
T1 ENABLED BASIC

去掉default后可以正常添加列，但是很多情况下业务逻辑是不允许的；

ALTER TABLE t1 ADD UPDATE_TIME DATE;

将表压缩级别basic改成oltp后可以正常添加字段；

SQL> alter table t1 compress for oltp;

对现有的数据不压缩，对以后产生的数据生效，数据量越大，速度越慢，测试1亿条数据，耗时30秒；

Alter table t1 move compress for oltp;

对现有的和以后的数据都生效，数据量越大，速度越慢，并且会产生排它锁，生产环境慎用；

SQL> SELECT table_name, compression, compress_for FROM user_tables where table_name='T1';
TABLE_NAME COMPRESS COMPRESS_FOR
---------- -------- ------------------------------
T1 ENABLED ADVANCED


SQL> ALTER TABLE t1 ADD UPDATE_TIME DATE DEFAULT SYSDATE NOT NULL ;
Table altered.

字段添加成功后也可以在改回basic;

SQL> alter table t1 compress;
Table altered.
SQL> SELECT table_name, compression, compress_for FROM user_tables where table_name='T1';
TABLE_NAME COMPRESS COMPRESS_FOR
---------- -------- ------------------------------
T1 ENABLED BASIC

同理，删除字段也会有这个问题

SQL> ALTER TABLE t1 drop column UPDATE_TIME;
ALTER TABLE t1 drop column UPDATE_TIME
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> ALTER TABLE t1 SET UNUSED COLUMN UPDATE_TIME;
Table altered.


SQL> ALTER TABLE t1 DROP UNUSED COLUMNS;
ALTER TABLE t1 DROP UNUSED COLUMNS
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> alter table t1 compress for oltp;
Table altered.


SQL> ALTER TABLE t1 DROP UNUSED COLUMNS;
Table altered.


SQL> alter table t1 compress;
Table altered.

欢迎关注我的公众号《IT小Chen》

###chenjuchao 20240425###