Row Migration refers to rows that were moved to another blocks due to an update making them too large to fit into their original blocks.
Oracle will leave a forwarding pointer in the original block so indexes will still be able to "find" the row. Note that Oracle does not discriminate between chained and migrated rows, even though they have different causes. A chained row is a row that is too large to fit into a single database data block.
For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces.
Some conditions that will cause row chaining are:
▪ Tables whose row size exceeds the blocksize
▪ Tables with long and long raw columns are prone to having chained rows
▪ Tables with more than 255 columns will have chained rows as Oracle break wide tables up into pieces. Detecting row chaining:
This query will show how many chained (and migrated) rows each table has:
SQL>SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt> 0;
To see which rows are chained:
SQL>ANALYZE TABLE tablename LIST CHAINED ROWS;
This will put the rows into the INVALID_ROWS table which is created by the utlvalid.sql script (located in $ORACLE_HOME/rdbms/admin).