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@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