My customer had a partitioned table that was previously compressed with FOR ALL OPERATIONS options.
One of the developers wanted to drop a columnn on a table, but receives the following error message:
ORA-39726: unsupported add/drop column operation on compressed tables
According to Oracle, this is expected behaviour. Oracle Support note 1068820.1 "Error ORA-39726 Drop Column Operation On Compressed Tables 10.2 Release" explains:
"In release 10g, a drop column attempt on a compressed table must raise an ORA-39726 error if the column has to be physically removed from the data blocks.
Hence "DROP COLUMN" and "DROP COLUMN UNUSED" are both illegal because they may have to touch the datablocks. SET UNUSED is OK because it is just a data
dictionary operation (as is DROP COLUMN on virtual columns)."
The Oracle support note concludes:
"In 11g it is allowed to drop columns from a compressed table IF compatible is set to 11.1 or higher AND table was created with the "compress for all OLTP" option but even in this situation there is no real drop but internally the database sets the column UNUSED to avoid long-running decompression and recompression operations."
My database unfortunately has compatible set to 11.0. So the only option at this point is to use the syntax
ALTER TABLE owner.table_name SET UNUSED COLUMN column_name;
This will render the column as good as dropped, another column can be added to the table with the same name.
The number of unused columns for a table can be tracked with
SELECT *
FROM USER_UNUSED_COL_TABS
WHERE TABLE_NAME='table_name';
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment