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

ORA-39726:

 unsupported add/drop column operation on compressed tables

問(wèn)題現(xiàn)象 :

Basic compression 下，增加字段時(shí)不能指定default ，否則報(bào)錯(cuò) ORA-39726；

問(wèn)題重現(xiàn)：
SQL> conn chenjch/a

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

問(wèn)題原因 :

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

解決方案：

SQL> SELECT table_name, compression, compress_for FROM user_tables where table_name='T1';

TABLE_NAME COMPRESS COMPRESS_FOR

---------- -------- ------------------------------

T1         ENABLED   BASIC

--- 去掉 default 后可以正常添加列，但是很多情況下業(yè)務(wù)邏輯是不允許的；

ALTER TABLE t1 ADD UPDATE_TIME DATE;

--- 將表壓縮級(jí)別 basic 改成 oltp 后可以正常添加字段；
--- 對(duì)現(xiàn)有的數(shù)據(jù)不壓縮，對(duì)以后產(chǎn)生的數(shù)據(jù)生效，數(shù)據(jù)量越大，速度越慢，測(cè)試 1 億條數(shù)據(jù)，耗時(shí) 30 秒；

SQL> alter table t1 compress for oltp;    
---- 對(duì)現(xiàn)有的和以后的數(shù)據(jù)都生效，數(shù)據(jù)量越大，速度越慢，并且會(huì)產(chǎn)生排它鎖，生產(chǎn)環(huán)境慎用；

sql> 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

同理，刪除字段也會(huì)有這個(gè)問(wèn)題

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.

  歡迎關(guān)注我的微信公眾號(hào)"IT小Chen"，共同學(xué)習(xí)，共同成長(zhǎng)?。?！