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 ??
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.