I came across a scenario where my table is basic compress(tablespace level compression), I read ORA 39726 Tips where we have to mark columns unused and then drop unused column
But after all, I get this error.
What is workaround to drop columns from compressed tables
My Understanding here is BASIC compression is COMPRESS keyword or compress for direct_load operation only is same thing
and OLTP compression tablespace level or compress for all operations is same
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> create table compression_test as select * from user_tables;
Table created.
SQL> select compression,compress_for from user_tables where table_name ='COMPRESSION_TEST';
COMPRESS COMPRESS_FOR
-------- ------------
ENABLED BASIC
SQL> alter table compression_test set unused(pct_increase,freelists);
Table altered.
SQL> alter table compression_test drop unused columns;
alter table compression_test drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
SQL>