Row Migration & Row chaining

What is Row Chaining
-----------------------------

Row Chaining happens when a row is too large to fit into a single database block. For example, if you use a 8KB block size for your database and you need to insert a row of 16KB into it, Oracle will use 2/3 blocks and store the row in chain of data blocks for that segment. And Row Chaining happens only when the row is being inserted.

you can show the block size of database by

SQL> conn / as sysdba
SQL> show parameter db_block_size


What is Row Migration
---------------------­­­­-----------

Row Migration Occurs when a row that originally fitted 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.



How can identified Row Chaining and row migration
-------------------------------------------------------------------------

There are 3 ways :-     1) by Analyze command
                                    2) USER_TABLES
                                    3) V$SYSSTAT