ORA-39726:unsupported add/drop column operation on compressed tables
ORA-39726:
Unsupported add/drop column operation on compressed tables
Problem phenomenon:
Under Basic compression, you cannot specify default when adding fields, otherwise an error ORA-39726 will be reported.
The problem reappears:
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
The cause of the problem:
Https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11630
Solution:
SQL > SELECT table_name, compression, compress_for FROM user_tables where table_name='T1'
TABLE_NAME COMPRESS COMPRESS_FOR
T1 ENABLED BASIC
-columns can be added normally after default is removed, but in many cases business logic is not allowed
ALTER TABLE t1 ADD UPDATE_TIME DATE
-fields can be added normally after changing the table compression level from basic to oltp
-do not compress the existing data and take effect on the data generated in the future. The larger the amount of data, the slower the speed. It takes 30 seconds to test 100 million pieces of data.
SQL > alter table T1 compress for oltp
-effective for both existing and future data, the larger the amount of data, the slower the speed, and it will produce exclusive locks, so be careful in the production environment.
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.
-after the field is added successfully, you can also change it back to 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
Similarly, deleting a field will have the same problem.
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.
Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!
