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
compressed.
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
the column:
alter table
compress for all operations;
alter table
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
column-level compression.
See here for details on
Oracle table compression errors.
The
oerr utility shows this on the ORA-39726 error:
ORA-39726: unsupported
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
column_name
- alter table table_name drop unused columns
If you are using table compression, try moving the table into a
non-compressed format:
- alter
table table_name move nocompress;
- alter table table_name drop column
column_name;
- alter table table_name compress;
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
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
buy it
for 30% off directly from the publisher.
|
