Shulou(Shulou.com)06/01 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.