Thanks - i will log an SR on Monday for the same issue but for 11.2.0.4 Brent On Fri, Feb 20, 2015 at 12:44 PM, Michael Cunningham < napacunningham@xxxxxxxxx> wrote: > I logged an SR with Oracle. FYI, the code to reproduce is below. > > > -- Create the test objects > #################################################### > drop sequence redef_table_size1_seq; > > drop sequence redef_table_size2_seq; > > drop table redef_table_size1 purge; > > drop table redef_table_size2 purge; > > create table redef_table_size1 > ( > id_1 number(15), > id_2 number(15), > id_3 number(15) > ); > > create unique index redef_table_size1_uix on redef_table_size1( id_1 ); > > create sequence redef_table_size1_seq start with 5000000000; > > create table redef_table_size2 > ( > id_1 number(15), > id_2 number(15), > id_3 number(15) > ); > > create unique index redef_table_size2_uix on redef_table_size2( id_1 ); > > create sequence redef_table_size2_seq start with 5000000000; > > -- Populate the test tables > ################################################### > insert into redef_table_size1 > select redef_table_size1_seq.nextval, > trunc( dbms_random.value( 20, 80 ) ), > trunc( dbms_random.value( 20, 80 ) ) > from all_objects > where rownum <= 20000; > > insert into redef_table_size2 > select redef_table_size2_seq.nextval, > trunc( dbms_random.value( 20, 80 ) ), > trunc( dbms_random.value( 20, 80 ) ) > from all_objects > where rownum <= 20000; > > commit; > > exec dbms_stats.gather_table_stats( user, 'redef_table_size1', cascade => > true ); > > exec dbms_stats.gather_table_stats( user, 'redef_table_size2', cascade => > true ); > > -- View storage info about the test objects > ################################### > select table_name, num_rows, avg_row_len from user_tables where table_name > like 'REDEF_TABLE_SIZE%'; > > select * from user_segments where segment_name like 'REDEF_TABLE_SIZE%'; > > select dbms_rowid.rowid_block_number( rowid ) as block#, count(*) > from redef_table_size1 > group by dbms_rowid.rowid_block_number( rowid ); > > select dbms_rowid.rowid_block_number( rowid ) as block#, count(*) > from redef_table_size2 > group by dbms_rowid.rowid_block_number( rowid ); > > -- REDEF on of the test tables > ################################################ > exec dbms_redefinition.redef_table( user, 'REDEF_TABLE_SIZE2', > table_part_tablespace => 'P6TBS', index_tablespace => 'P6TBS', > lob_tablespace => 'COMMONTBS' ); > > -- View storage info about the test objects > ################################### > -- This is where the changes can be seen > ################################### > select table_name, num_rows, avg_row_len from user_tables where table_name > like 'REDEF_TABLE_SIZE%'; > > select * from user_segments where segment_name like 'REDEF_TABLE_SIZE%'; > > select dbms_rowid.rowid_block_number( rowid ) as block#, count(*) > from redef_table_size1 > group by dbms_rowid.rowid_block_number( rowid ); > > select dbms_rowid.rowid_block_number( rowid ) as block#, count(*) > from redef_table_size2 > group by dbms_rowid.rowid_block_number( rowid ); > > > On Fri, Feb 20, 2015 at 10:08 AM, Brent Day <coloradodba@xxxxxxxxx> wrote: > >> 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@xxxxxxxxx> 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 >>> >> >> > > > -- > Michael Cunningham >