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.
Smart way of Technology 