This discussion is archived
2 Replies Latest reply: Sep 4, 2013 5:21 AM by saurabh RSS

Drop column from compressed table

Verdi Newbie
Currently Being Moderated
NLSRTL11.2.0.3.0Production
Oracle Database 11g Enterprise Edition11.2.0.3.064bit Production
PL/SQL11.2.0.3.0Production
TNS for Linux:11.2.0.3.0Production

 

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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points