Row Chaining and Migrating

When a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

rajesh@10GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> create table t(x number,y varchar2(4000),z varchar2(4000));

Table created.

Elapsed: 00:00:00.03
rajesh@10GR2> insert into t(x) values(1);

1 row created.

Elapsed: 00:00:00.00
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> exec show_space(USER,'T','TABLE');
l_total_blocks****************  16
l_total_bytes*****************  131072
l_unused_blocks***************  12
l_unused_bytes****************  98304
l_last_used_extent_file_id****  7
l_last_used_extent_block_id***  377
l_last_used_block*************  4
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  0
l_fs2_bytes*******************  0
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  1
l_fs4_bytes*******************  8192
l_full_blocks*****************  0
l_full_bytes******************  0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
rajesh@10GR2> select rowid from t;

ROWID
------------------
AAAjDIAAHAAAAF8AAA

Elapsed: 00:00:00.03
rajesh@10GR2>

Now table 'T' consumes 4 blocks to store its data. Now lets add more data's to current row so that overall length increases and data spans to new block.

rajesh@10GR2> update t
  2  set y = rpad('*',4000,'*'),
  3     z = rpad('*',4000,'*')
  4  where x = 1;

1 row updated.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.04
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
rajesh@10GR2> exec show_space(USER,'T','TABLE');
l_total_blocks****************  16
l_total_bytes*****************  131072
l_unused_blocks***************  6
l_unused_bytes****************  49152
l_last_used_extent_file_id****  7
l_last_used_extent_block_id***  377
l_last_used_block*************  10
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  0
l_fs2_bytes*******************  0
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  5
l_fs4_bytes*******************  40960
l_full_blocks*****************  2
l_full_bytes******************  16384

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

Now Unused blocks got reduced from 12 to 6, means some data has migrated to new blocks but the rowid of this migrated rows remains unchanged.

rajesh@10GR2> select rowid from t;

ROWID
------------------
AAAjDIAAHAAAAF8AAA

Elapsed: 00:00:00.01
rajesh@10GR2>