Re: Oracle9.2: Data segment compression bug

It's not a but, it's expected behaviour.

The documentation is almost non-existent, but you're only expected to compress tables (or partitions) before making them read-only.

You should also note that when you compress a table, the PCTFREE switches to zero - it's a little clue that you aren't expected to do any normal DML on the table.

I believe there may be a whitepaper about it on OTN by now.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Vsevolod Afanassiev wrote in message
<4f7d504c.0211142142.4c1b75d4_at_posting.google.com>...


>It seems that when a table is created with COMPRESS attribute

>and then populated via conventional INSERT, the

>compression does not work. However, if a table

>is populated via direct load insert (using SQL*Loader or /*+ APPEND

*/


>hint), then compression works.

>This can be seen from the fact that the segment size is approx. twice

>smaller (table consists of multiple small extents of the same size).

>

>After a table is created, then

>ALTER TABLE name MOVE COMPRESS;

>will compress it no matter how it was created.

>

>The happens for both partitioned and non-partitioned tables

>and locally managed and dictionary-managed tablespaces.

>

>Environment:

>Sun Solaris 8

>Oracle 9.2.0.2.0 (32bit), 16k blocks