Systems Engineering and RDBMS

Dropping a column from a compressed table in Oracle

Posted by decipherinfosys on June 9, 2010

If you are using compression in Oracle, you might be already aware of how to drop a column from a compressed table but in case you are not – here is a simple series of steps that you can follow to drop it:

SQL> CREATE TABLE TEST COMPRESS for all operations AS SELECT * FROM INVOICE_HDR;

Table created.

SQL> ALTER TABLE TEST ADD (DOCK_ZONE VARCHAR2 (100));

Table altered.

Now, let’s try to drop it using the ALTER TABLE command:

SQL> ALTER TABLE TEST DROP COLUMN DOCK_ZONE;
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

As you can see from above, we get the error back.  Now, let’s set that column to unused:

SQL> ALTER TABLE TEST SET UNUSED (DOCK_ZONE);

Table altered.

And we can drop it now:

SQL> ALTER TABLE TEST DROP unused columns;

Table altered.

6 Responses to “Dropping a column from a compressed table in Oracle”

  1. Ram said

    This doesn’t work for me. I still get the same error message:

    SQL> alter table fact_ext_rollup drop unused columns;
    alter table fact_ext_rollup drop unused columns
    *
    ERROR at line 1:
    ORA-39726: unsupported add/drop column operation on compressed tables

    • Ram, Did you set the column to be unused status first as shown in the post? If that does not work for you for some reason, you can try to move the table into a non-compressed format and then drop the column and compress again.

  2. Tom Forsyth said

    Here is what I got when I tried this technique. The last sentence suggests to me that the SET UNUSED option of ALTER TABLE effectively drops the column(s) on which it is performed. If that is the case, then it and DROP COLUMN represent two ways of eliminating a column from a table, with SET UNUSED being the required syntax when the table is compressed.

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

  3. Anonymous said

    SET UNUSED doesnt word in partition table !
    what can I do ?

  4. Josefine said

    It also depends on the type of compression. For a table compressed for direct- path inserts drop column isn’t possible, but if the table is compressed for all operations you can use SET UNUSED/ DROP UNUSED. As far as I know…

  5. Key said

    For non-partitioned tables. In Oracle 11.2.0.3 “drop column” works for OLTP compression, so the problem shown does not exists any more. For Basic compression, it does. I can suggest to do only SET UNUSED. If you need physical drop (do you really?), you need talble MOVE to be done.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: