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

ISSUE:
ORA-39726 is hit when adding column with default value to basic compressed table.

CAUSE:
compressed table will not allow some operation, which are allowed in uncompressed table.

FAILED STATEMENT:
alter table yt add new_column number(3) default 0;

WORK AGROUND:
Case 1: old records not need have default vaule:
alter table yt add new_column number(3);
alter table yt modify new_column default 0;

Case 2: old record need have default value ;
this has exact same effect on compress table as on uncompress table.
alter table yt add new_column number(3) default 0 not null;
alter table yt modify new_column null;

DROP COLUMN FROM COMPRESS TABLE:
alter table yt_compress set unused column new_column;
if you want to get ride of the data from space, do a table move.  And
alter table yt_compress drop unused columns;



Example:

test@TEST.test> create table yt_compress compress as select owner, table_name from dba_tables where owner='SYS';

Table created.

test@TEST.test> create table yt_uncompress as select owner, table_name from dba_tables where owner='SYS';

Table created.

test@TEST.test> select count(*) from yt_compress;

  COUNT(*)
----------
       985

test@TEST.test> select count(*) from yt_uncompress;

  COUNT(*)
----------
       985

test@TEST.test> alter table yt_compress add new_column number(3) default 0;
alter table yt_compress add new_column number(3) default 0
                            *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


test@TEST.test> alter table yt_uncompress add new_column number(3) default 0;

Table altered.

test@TEST.test> select count(*) from yt_uncompress where new_column is null;

  COUNT(*)
----------
         0

test@TEST.test> select count(*) from yt_uncompress where new_column =0;

  COUNT(*)
----------
       985

test@TEST.test> alter table yt_compress add new_column number(3);

Table altered.

test@TEST.test> alter table yt_compress modify new_column default 0;

Table altered.

test@TEST.test> select count(*) from yt_compress where new_column =0;

  COUNT(*)
----------
         0

test@TEST.test> select count(*) from yt_compress where new_column is null;

  COUNT(*)
----------
       985

test@TEST.test> alter table yt_compress add new_column_1 number(3) default 0 not null;

Table altered.

test@TEST.test> alter table yt_compress modify new_column_1 null;

Table altered.

test@TEST.test> select count(*) from yt_compress where new_column is null;

  COUNT(*)
----------
       985

test@TEST.test> select count(*) from yt_compress where new_column_1 is null;

  COUNT(*)
----------
         0

test@TEST.test> select count(*) from yt_compress where new_column_1 =0;

  COUNT(*)
----------
       985


test@TEST.test> alter table yt_compress drop column new_column;
alter table yt_compress drop column new_column
                                    *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


test@TEST.test> alter table yt_compress set unused column new_column;

Table altered.

test@TEST.test> desc yt_compress
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                 NOT NULL VARCHAR2(30)
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 NEW_COLUMN_1                                                   NUMBER(3)

test@TEST.test>

reference:

http://www.dba-oracle.com/oracle11g/sf_Oracle_11g_Data_Compression_Tips_for_the_DBA.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:69076630635645