Tutorial : Dropping a column from a compressed table in Oracle

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.

Post Tagged with

Leave a Reply