Oracle Database Discussions

Friends

I am trying to add columns to a table which is compressed. Since Oracle treats compressed tables as Object tables, I cannot add columns directly so I tried to uncompress table first and then add columns. This doesnt seems to work.

What could be issue.

Thanks
Vishal V.

Script to test is here and results are below.

-- Test1 => add columns to uncompressed table -> Success
DROP TABLE TAB_COMP;
CREATE TABLE TAB_COMP(ID NUMBER) NOCOMPRESS;
ALTER TABLE TAB_COMP ADD (NAME VARCHAR2(10));

-- Test2 =. try adding columns to compressed tables, uncompress it and then try again -> Fails
DROP TABLE TAB_COMP;
CREATE TABLE TAB_COMP(ID NUMBER) COMPRESS;
ALTER TABLE TAB_COMP ADD (NAME VARCHAR2(10));
ALTER TABLE TAB_COMP move NOCOMPRESS;
ALTER TABLE TAB_COMP ADD (NAME VARCHAR2(10));

SQL> -- Test1 => add columns to uncompressed table -> Success
SQL> DROP TABLE TAB_COMP;

Table dropped.

SQL> CREATE TABLE TAB_COMP(ID NUMBER) NOCOMPRESS;

Table created.

SQL> ALTER TABLE TAB_COMP ADD (NAME VARCHAR2(10));

Table altered.

SQL> 
SQL> -- Test2 =. try adding columns to compressed tables, uncompress it and then try again -> Fails
SQL> DROP TABLE TAB_COMP;

Table dropped.

SQL> CREATE TABLE TAB_COMP(ID NUMBER) COMPRESS;

Table created.

SQL> ALTER TABLE TAB_COMP ADD (NAME VARCHAR2(10));
ALTER TABLE TAB_COMP ADD (NAME VARCHAR2(10))
                          *
ERROR at line 1:
ORA-22856: cannot add columns to object tables


SQL> ALTER TABLE TAB_COMP move NOCOMPRESS;

Table altered.

SQL> ALTER TABLE TAB_COMP ADD (NAME VARCHAR2(10));
ALTER TABLE TAB_COMP ADD (NAME VARCHAR2(10))
                          *
ERROR at line 1:
ORA-22856: cannot add columns to object tables