Current Article

Oracle Compress Table Problem ?

By admin on Jan 30, 2008 in Oracle

An interesting finding…

create table test_table
(
col_1 number
)
compress;

alter table test_table add (col_2 number);

insert into test_table values(1,2);

After this, try to drop one column

alter table test_table drop column col_2;

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

This is expected as the table is still compressed.

alter table test_table nocompress;

alter table test_table drop column col_2;

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

Even I set the table to nocompress, still the same error appeared.

Then I do this

alter table test_table set unused(col_2);

alter table test_table drop unused columns;

I can drop the column this way.

But then if I want to add another column with default value..

alter table test_table add (col_2 number default 1);

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

How do I add the column with default value ??

Popularity: 3% [?]

Geoff | Jun 30, 2009 | Reply

I realise this is over a year ago, but thought I’d respond anyway for future Googlers…

You do it in two steps – add a column and then alter it to specify a default.

eg.
SQL> alter table blah add(a1 number);

Table altered.

SQL> alter table blah modify (a1 default 1);

Table altered.
Dipesh | Jul 15, 2010 | Reply

In compressed tables you cannot drop column but you can make it unused only.
ALTER TABLE compressed_table SET unused column test_column;