Partition, compress and drop column (ORA-39726)

Testing 1: Compression, drop column encountered "ORA-39726: unsupported add/drop column operation on compressed tables" error. After uncompress all the partitions, issue resolved.

SQL> drop table t1_compression purge;

Table dropped.

SQL> create table t1_compression (id number, name varchar2(30))
  2  partition by range (id) interval (10000) (partition p1 values less than (10000) ) compress;

Table created.

SQL> insert into t1_compression select object_id,object_name from dba_objects;

91483 rows created.

SQL> commit;

Commit complete.

SQL> col partition_name for a20
SQL> select partition_name,compression,compress_for from user_tab_partitions
  2  where table_name='T1_COMPRESSION';

PARTITION_NAME       COMPRESS COMPRESS_FOR
-------------------- -------- ------------------------------
P1                   ENABLED  BASIC
SYS_P658             ENABLED  BASIC
SYS_P662             ENABLED  BASIC
SYS_P663             ENABLED  BASIC
SYS_P665             ENABLED  BASIC
SYS_P657             ENABLED  BASIC
SYS_P661             ENABLED  BASIC
SYS_P664             ENABLED  BASIC
SYS_P660             ENABLED  BASIC
SYS_P659             ENABLED  BASIC

10 rows selected.
SQL> alter table t1_compression drop column name;
alter table t1_compression drop column name
                                       *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table t1_compression set unused column name;

Table altered.

SQL> desc t1_compression;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER


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


SQL>  alter table t1_compression move partition p1 nocompress;

Table altered.

SQL>  alter table t1_compression move partition SYS_P657  nocompress;

Table altered.

SQL> select partition_name,compression,compress_for from user_tab_partitions
  2  where table_name='T1_COMPRESSION';

PARTITION_NAME       COMPRESS COMPRESS_FOR
-------------------- -------- ------------------------------
P1                   DISABLED
SYS_P658             DISABLED
SYS_P662             DISABLED
SYS_P663             DISABLED
SYS_P665             DISABLED
SYS_P657             DISABLED
SYS_P661             DISABLED
SYS_P664             DISABLED
SYS_P660             DISABLED
SYS_P659             DISABLED

10 rows selected.

SQL> alter table t1_compression drop unused columns;

Table altered.

Testing 2: Compression for OLTP, drop column without error

SQL> drop table t1_compression purge;

Table dropped.

SQL> create table t1_compression (id number, name varchar2(30))
  2  partition by range (id) interval (10000) (partition p1 values less than (10000) ) compress for oltp;

Table created.

SQL> insert into t1_compression select object_id,object_name from dba_objects;

91483 rows created.

SQL> commit;

Commit complete.

SQL> select partition_name,compression,compress_for from user_tab_partitions
  2  where table_name='T1_COMPRESSION';

PARTITION_NAME       COMPRESS COMPRESS_FOR
-------------------- -------- ------------------------------
SYS_P673             ENABLED  ADVANCED
SYS_P671             ENABLED  ADVANCED
SYS_P670             ENABLED  ADVANCED
SYS_P667             ENABLED  ADVANCED
SYS_P674             ENABLED  ADVANCED
SYS_P672             ENABLED  ADVANCED
SYS_P669             ENABLED  ADVANCED
SYS_P666             ENABLED  ADVANCED
P1                   ENABLED  ADVANCED
SYS_P668             ENABLED  ADVANCED

10 rows selected.

SQL> alter table t1_compression drop column name;

Table altered.