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

ORA-39726:

 unsupported add/drop column operation on compressed tables

 

问题现象:

Basic compression下，增加字段时不能指定default，否则报错ORA-39726；

问题重现：
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

问题原因:

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后可以正常添加列，但是很多情况下业务逻辑是不允许的；

ALTER TABLE t1 ADD UPDATE_TIME DATE;

---将表压缩级别basic改成oltp后可以正常添加字段；
---对现有的数据不压缩，对以后产生的数据生效，数据量越大，速度越慢，测试1亿条数据，耗时30秒；

SQL> alter table t1 compress for oltp;    
----对现有的和以后的数据都生效，数据量越大，速度越慢，并且会产生排它锁，生产环境慎用；

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

 

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

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.

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2153428/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-2153428/