Re: Tables Larger When using DBMS_REDEFINITION

From: Brent Day <coloradodba_at_gmail.com>
Date: Fri, 20 Feb 2015 11:08:37 -0700
Message-ID: <CAEz8shwvKoFBL=AcWsFc_cFpkc+4-FcHR5vQkBF+JsGeP+0Y3Q_at_mail.gmail.com>

Yes I saw this with regular tables and using advanced compression. I didn't log an SR but talked to some of the internal Oracle techs I have access to and never got any real answers.

In fact for our compression efforts we do a CTAS to get full use of compression. One example I can give was a small table that used 101k blocks and using DBMS_REDEFINITION with ADVANCED COMPRESSION for ALL OPERATIONS the table had 127k blocks.

If you decide to log an SR let me know and I will log one as well.

Brent

On Fri, Feb 20, 2015 at 10:22 AM, Michael Cunningham < napacunningham_at_gmail.com> wrote:

> I'm going to log a bug with Oracle on this, but has anyone experienced
> this?
>
> When I use DBMS_REDEFINTION the finished size of a table is larger than it
> was prior to redef. It looks like the problem lies in the AVG_ROW_LEN. For
> some reason the redef code is inserting the rows into the table and the
> AVG_ROW_LEN is larger resulting in less rows per block.
>
> In some cases the table is as much as 55% larger. BTW, I only see this on
> some tables. When this does happen on a table it is consistent no matter
> how many times I run the redef on that table.
>
> Also, I tested on an offending table with a simple CTAS and the size of
> the new table is what I expect.
>
> --
> Michael Cunningham
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 20 2015 - 19:08:37 CET