How to drop an Unused Column from a compressed table in an Oracle Database(Fix: ORA-39726).

How to drop an Unused Column from a compressed table in an Oracle Database (Fix : ORA-39726).

Overview: 

There are some situations where we required to Drop a column from a compressed table per business need.
In this article, I would walk you through steps to perform this task.

You may receive below error when you try DROPPING a column from a compressed table

-------  Drop unused column using simple ALTER command

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

--------  Check what compression is for table. 

SQL> select table_name,COMPRESSION,COMPRESS_FOR from all_tables where table_name='SM_ASSET_MONTH_FACT_STAGE';

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
SM_ASSET_MONTH_FACT_STAGE       ENABLED  QUERY HIGH

--------  DECOMPRESS table :: Now, Lets try to DECOMPRESSING this table and drop the column again

SQL> alter table DWUSER.SM_ASSET_MONTH_FACT_STAGE nocompress;

Table altered.

------- Now try dropping the column 

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


--- We get same error

Cause:  ‘ALTER TABLE <Table_name> NOCOMPRESS‘ applies to future records.
Here we have an option to mark the column as 'UNUSED' and prevent it appearing from the table definition. When we set a column as 'UNUSED', this will update the table definition and does not physically remove the column.

Most of the times, 'SET UNUSED' is used in scenarios where it is expected that drop column would take huge amount of time. Space can be reclaimed only if we either DROP the column or drop the UNUSED column if it's already 'SET UNUSED'.

-------  Let us mark the column UNUSED and force it disappear from the table defination. 
-- Decompress table

SQL> alter table DWUSER.SM_ASSET_MONTH_FACT_STAGE nocompress;

Table altered.

SQL> select table_name,COMPRESSION,COMPRESS_FOR from all_tables where table_name='SM_ASSET_MONTH_FACT_STAGE';

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
SM_ASSET_MONTH_FACT_STAGE       DISABLED

SQL> alter table DWUSER.SM_ASSET_MONTH_FACT_STAGE move nocompress parallel 4;

Table altered.

SQL>
SQL> alter table DWUSER.SM_ASSET_MONTH_FACT_STAGE drop unused columns;

Table altered.

SQL>  alter table DWUSER.SM_ASSET_MONTH_FACT_STAGE compress;

Table altered.

Conclusion

In this article we have learnt How to drop an un column from a compressed table.