Compression in 10g

In v9 of Oracle, there are all sorts of restrictions on what DDL you can perform on tables that are set for compression. In v10, some of the restrictions have been lifted - which of course makes this part of the database worth of some playing around.

For example, lets see what happens when we try to drop a column on a compressed partitioned table.

SQL> create table PARTAB
  2   ( x number , y number)
  3  partition by range ( x )
  4   ( partition p1 values less than (100),
  5     partition p2 values less than (200) );

Table created.

SQL> alter table PARTAB compress;

Table altered.

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

Now thats fair enough...and should be easy enough to solve. We'll just set the table to being uncompressed...

SQL> alter table PARTAB nocompress;

Table altered.

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

Ooopppsss....