Monthly Archives: March 2010

Unable to add columns in a table

SQL Error Adding multiple column in one batch

HY000 (22856): [DataDirect][ODBC Oracle driver][Oracle]ORA-22856: cannot add columns to object tables

This is the error you get when adding columns to a compressed tables in Oracle 9i. This is a bug (#2421054) documented on Oracle Support (Doc. ID 217292.1).
Solution:

  1. Take the backup preferably export *dump* of table.
  2. Take the script backup of indexes and table.
  3. drop the table.
  4. create the table with nocompress option (alter table nocompress)
  5. import the *dump* with ignore=y option. Now you can add columns, this error specifically can be seen during Siebel DDL sync activity.
Advertisements