Alter Table Problem

[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.

[davey23uk]

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.

[tamilselvan]

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:

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

[slimdave]

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