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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s