Oracle Database Discussions

Hello,

I read about how to drop column from a compressed table - first set it unused and then drop the unused columns. However, in the example below on the database I ran it, it does not work. Please, can you tell me WHEN this approach does not work. What it is dependent on - parameters or something else. Why I cannot drop the unused columns?

And the example along with the errors:

create table tcompressed compress as select * from all_users;

> table TCOMPRESSED created.

alter table tcompressed add x number;

> table TCOMPRESSED altered.

alter table tcompressed drop column x;

>

Error report:

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

39726. 00000 -  "unsupported add/drop column operation on compressed tables"

*Cause:    An unsupported add/drop column operation for compressed table

           was attemped.

*Action:   When adding a column, do not specify a default value.

           DROP column is only supported in the form of SET UNUSED column

           (meta-data drop column).

alter table tcompressed set unused column x;

> table TCOMPRESSED altered.

alter table tcompressed drop unused columns;

>

Error report:

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

39726. 00000 -  "unsupported add/drop column operation on compressed tables"

*Cause:    An unsupported add/drop column operation for compressed table

           was attemped.

*Action:   When adding a column, do not specify a default value.

           DROP column is only supported in the form of SET UNUSED column

           (meta-data drop column).

As you can see even after altering the table by setting the column X as unused I still cannot drop it by using DROP UNUSED COLUMNS.

Thank you.