Online redefinition depends on materialized view technology and in
your example has to add a column to your table as it copies it to hold
the original rowid; this column is ultimately set to unused:
SQL> select table_name, column_name, data_type, data_length,
hidden_column from user_tab_cols order by table_name, column_id;
TABLE_NAME COLUMN_NAME DATA_TYPE
DATA_LENGTH HID
-------------------- --------------------------------
-------------------------------- ----------- ---
REDEF_TABLE_SIZE1 ID_1 NUMBER 22 NO
ID_2 NUMBER 22 NO
ID_3 NUMBER 22 NO
REDEF_TABLE_SIZE2 ID_1 NUMBER 22 NO
ID_2 NUMBER 22 NO
ID_3 NUMBER 22 NO
SYS_C00004_15022022:38:27$ VARCHAR2
255 YES
The stored rowid is 18 characters - which is why it's so noticeable
for your example which starts with an avg_row_len of 13.
It's part of the implementation, not a bug.
If you create a primary key constraint on the table this rowid column
won't be needed as the materialized view log will be "using PK"
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
------------------------------------------------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx]
on behalf of Michael Cunningham [napacunningham@xxxxxxxxx]
*Sent:* 20 February 2015 17:22
*To:* oracle-l@freelists org
*Subject:* Tables Larger When using DBMS_REDEFINITION
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
