dropping column from a huge table, ORA-39726: unsupported add/drop column operation on compressed tables Options

[sishyadba]

Hi

I am trying to drop a column from a huge partitioned table (noncompressed including partitions).
I am working on 11gR2 database for information.

i used below approach
1. alter table <tab_name> set t1 unused column;

2. alter table <tab_name> drop unused columns;

then i got the below error message

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

It seems to be strange to me since this is the work around suggested by Oracle Guru's Burleson and Tom in their articles.

Since first statement did work i could be able to add another column with the same name but still don't want the unused column on the table.

Can anyone of you suggest me what would be the reason for this error?

Sri

[SteveC]

It is well documented at My Oracle Support.

[sishyadba]

Thanks a lot for your reply Steve. Since am a developer i don't have the SR login credentials. I would really appreciate if anyone could copy paste the content of the article.

Thanks again

Sri

[sishyadba]

anyway found the solution ..since 11g supports the advanced compression ("compress for all operations")

i did

alter table <tab_name> compress for all operations;

then i could be able to drop the unused columns using below command, Hats-off to the 11g improvements

alter table <tab_name> drop unused columns;

Thanks again for everyone who tried to help.

Sri

[SteveC]

Posting MOS content violates TOS with them.

[burleson]

Hi Sri,

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

See here: http://www.dba-oracle.com/t_ora_39726_tips.htm


Glad you got it working . . .