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 then 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:
SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0;
To see which rows are chained:
ANALYZE TABLE tablename LIST CHAINED ROWS;
This will put the rows into the CHAINED_ROWS table which is created by the utlchain.sql script (in $ORACLE_HOME/rdbms/admin).
SELECT * FROM chained_rows;
|Glossary of Terms