2 Replies Latest reply on Sep 4, 2013 12:22 PM by 940315

    Drop column from compressed table

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