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

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

Error

SQL> alter table test1 drop column address;
alter table test1 drop column address
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

Check the Table status

SQL> SELECT table_name,compression, compress_for from user_tables where table_name = 'TEST1';

TABLE_NAME COMPRESS COMPRESS_FOR
---------- -------- --------------
TEST1      ENABLED  BASIC

--Uncompress the table
SQL> ALTER TABLE test1 nocompress;
Table altered.

-- Same error getting
SQL> alter table test1 drop column address;
alter table test1 drop column address
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

Solution
uncompress the existing data with moving the data
Move command with help uncompress existing data, so the data gets uncompressed during the copy.

SQL> alter table test1 move nocompress;
Table altered.

Now try the drop command:

SQL> alter table test1 drop column address;
Table altered.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.