Tuesday, May 10, 2011

ORA-39726 tying to drop column on compressed table (10g)

You get ORA-39726: unsupported add/drop column operation on compressed tables when you try to drop a column on a compressed table. In order to avoid drop and recreate the table and adding all indexes , constraints etc you can set the column unused. alter table mytable set unused (mycol1,mycol2); This statement does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed. References: Oracle® Database Administrator's Guide 10g Release 2 (10.2) Chapter 15 -- Managing Tables Topic : Altering Columns Note: In 11g you can also drop columns on compressed tables If you enable compression for all operations on a table. If you enable compression for direct-path inserts only, you cannot drop columns.

No comments:

Post a Comment