Question: I am getting ORA-39726 error when a
dropping a column on a non-compressed table.
ORA-39726: unsupported add/drop column operation
on compressed tables
I am confused because I did not think that this table was
What causes are there for the ORA-39726 error?
Answer: In 11g and beyond, if you have a
compressed table/column the following procedure will work to drop
compress for all operations;
drop unused columns;
It is not possible to get the
ORA-39726 error on a non-compressed table.
However, Oracle has had
several "types" of table compression over the years, including
See here for details on
Oracle table compression errors.
oerr utility shows this on the ORA-39726 error:
add/drop column operation on compressed tables
Cause: An unsupported add/drop column operation
for compressed table was attempted.
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).
When dropping a column you can avoid the ORA-39726 error by
setting the column to unused status:
- alter table table_name set unused column
- alter table table_name drop unused columns
If you are using table compression, try moving the table into a
table table_name move nocompress;
- alter table table_name drop column
- alter table table_name compress;
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.