Re: Compressed tables

hey astalavista,

Oracle version 9.2 has a tiny bug in compressed tables: Once compressed if you try to do "alter table add column" you get a interesting error message:

   ORA-22856: cannot add columns to object tables

It you try to "uncompress" it and add a column you will get the same error. So now you're stuck with a 10 billion records compressed table which cannot be altered. ;-)

It's Bug 2421054 ,on metalink :
* In 9i errors are reported for ADD/DROP   but the text may be misleading, eg:
   ADD column fails with "ORA-22856: cannot add columns to object tables"    DROP column fails with "ORA-12996: cannot drop system-generated virtual column"

• Note that a table which was previously marked as compress which has now been altered to NOCOMPRESS also signals such errors as the underlying table could still contain COMPRESS format datablocks.

It is fixed in 10G though and - the fix is considered an enhancement - well, well.

Cheers, Roelof

"astalavista" <spam_at_nowhere.com> schreef in bericht > Hi,
> We have a datawarehouse
> and we have some space problem.
> I thought about using compressed tables
> for rarely accessed tables ( archived data )
>
> Is it a reliable technology ? What are the limitations ?
> Any advice ?
>
> Thanks in advance
> Oracle 9.2.0.5 on AIX 5.1
Received on Sun Oct 09 2005 - 08:20:11 CDT