You can hit this error during drop column on your compressed tables

For the 10.2 there is no solution for this error. But there are some options to workaround for can pass this error.

These are:

Upgrade your db to 11gR2
Can keep unused columns on tables
You may recreate table without such columns which you try to drop
Do not to use compress

For 11g here is the my demo:

oracle@helios> create table test_table compress as select * from dba_users;

Table created.

oracle@helios> alter table test_table add name varchar2;

Table altered.

oracle@helios> alter table test_table drop column name;
alter table test_table drop column name
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

oracle@helios> alter table test_table set unused column name;

Table altered.

oracle@helios> alter table test_table drop unused columns;

Table altered.

If you still keep hiting same error you can hit bug 9163477.

Reference:
Error ORA-39726 Drop Column Operation On Compressed Tables 10.2 Release [ID 1068820.1]
Bug 9163477 – error ora-39726 while dropping column on compressed partitioned table [ID 9163477.8]

Advertisements