Alter Table Problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Alter Table Problem

  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Alter Table Problem

    Hello,

    I'm trying to alter a table and I'm getting the folowing error:

    ORA-22856: cannot add columns to object tables

    the sql I'm performing is:
    ALTER TABLE STATSOWNER.LOG_TRANSACTION_DETAILS
    ADD (HBW VARCHAR(1))

    as far as I know it should work, right?

    So tried to persue this a little bit more and I found out the table I'm trying to alter is in fact a synonym.

    So I got the actual table name ,which is the same as the synonym, and found that I'm logging in with its owner so there should be no problem, right?

    Can anyone help me?

    Thanks,
    Vanessa.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    ORA-22856: cannot add columns to object tables
    Cause: An attempt was made to add columns to an object table. Object tables cannot be altered to add columns since its definition is based on an object type.
    Action: Create a new type with additional attributes, and use the new type to create an object table. The new object table will have the desired columns.

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by vsanta
    Hello,

    I'm trying to alter a table and I'm getting the folowing error:

    ORA-22856: cannot add columns to object tables

    the sql I'm performing is:
    ALTER TABLE STATSOWNER.LOG_TRANSACTION_DETAILS
    ADD (HBW VARCHAR(1))

    as far as I know it should work, right?

    So tried to persue this a little bit more and I found out the table I'm trying to alter is in fact a synonym.

    So I got the actual table name ,which is the same as the synonym, and found that I'm logging in with its owner so there should be no problem, right?

    Can anyone help me?

    Thanks,
    Vanessa.
    I think you are adding a column to object table.
    If it is a normal table, you will not see a problem.
    See below:
    Code:
    SQL> create table t1 (id int) ;
    
    Table created.
    
    SQL> alter table t1 add (dummy varchar(1)) ;
    
    Table altered.
    
    
    SQL> create public synonym t1 for tamil.t1 ;
    
    Synonym created.
    
    SQL> alter table t1 drop column dummy ;
    
    Table altered.
    
    SQL>  alter table t1 add (dummy varchar(1)) ;
    
    Table altered.
    
    SQL> desc t1
     Name                              Null?    Type
    -------------------------- -------- ----------------
     ID                                             NUMBER(38)
     DUMMY                                      VARCHAR2(1)
    Tamil

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The table is probably compressed -- in 10g I believe you can add columns to compressed tables but not in 9i.

    You have to uncompress the table to add the new columns, or create a new table by selecting from it (and trandsfer name/indexes/ grants etc). Add in a few spare columns as well -- you can rename them and increase width of varchar2's without a problem even though the table is compressed
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width