ALTER TABLE causes ORA-22856

[Chopsaki]

OK this is probably something stupid but my DB will not let me alter tables at all. It always retuns the following error -

ORA-22856: cannot add columns to object tables

Doesn't matter which user I am logged in as either.

Any ideas?

foo

 

[sybaseguru]

Hi foo,

The issue I think is that you are trying to add columns to an object table. Unfortunately you cannot modify the structuure of an object table because the definition of this table is based upon an object type. What you need to do is to create a new type with additional attributes, and use the new type to create a new object table. The new object table will have the desired columns.

Hope this helps

 

[Chopsaki]

I made the table using a simple create table script (in sqlplus). It shouldn't be based on an object.

foo

 

[sybaseguru]

Foo,

as user scott/tiger can you log in and create a simple table and try to alter it and add a column. This is strange!

 

[Chopsaki]

OK, I do that... but the problem remains in a separate tablespace. The whole thing is hosed... I need help. I cannot alter tables at all under the one tablespace.

foo

 

[Chopsaki]

OK, I can do that, I meant to say.

foo

 

[sybaseguru]

Ok can you please do the following:

select * from user_tablespaces where tablespace_name IN ('TOOLS','<MY_TABLESPACE>')
I would like to see what is different about this tablespace. Is this the user's default tablespace?

 

[Chopsaki]

No, I made this tablespace... it worked before...
SEGMENT_SPACE_MANAGEMENT is the only difference. It is set to manual on mine.

foo

 

[sybaseguru]

OK, Foo, &quot;on this tablespace&quot; of yours can you create a table and add a column to it and what has changed since it worked. Can you also use exp to export the affected table
exp xxx/yyyy file=tab.dmp log=tab.log tables=tab
and do
imp xxx/yyyy file=tab.dmp full=yes indexfile=tab.sql
and send the tab.sql output

 

[sem]

Can you post table structure (DESC command output) as well as your ALTER statement?

Regards, Dima

 

[Vitalik]

If you have enough rights for the table you're having problem with, then the problem might be in a tablespace. It may be in a READ ONLY mode.

So try ALTER TABLESPACE to switch it to READ/WRITE.

Best Regards,
Vitaliy