How to resolve the row chaining present in the Oracle database

Row Chaining When a row inserted into database does not fit in one block and used another blocks for data insertion is called the row chaining. Means one row of table is inserted into multiple blocks which caused row chaining. Oracle need to access multiple blocks of the hard disk to access one row.

Following Steps to find the row chaining present in database

1. Find the row chaining present in oracle database.

SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0 and table_name not in ( select table_name from dba_tab_columns where data_type in ('RAW','LONG RAW')) order by chain_cnt desc;

2. Check which rows are chained.

ANALYZE TABLE tablename LIST CHAINED ROWS;

Note: This will put the rows into the INVALID_ROWS table which is created by the utlvalid.sql script (in $ORACLE_HOME/rdbms/admin).

SELECT * FROM chained_rows;

Steps for removing Row Chaining from database

1. Execute the ANALYZE command.

ANALYZE TABLE LIST CHAINED ROWS;

2. Create new table with chained rows.

CREATE TABLE chained_temp AS
SELECT * FROM
WHERE rowid IN (SELECT head_rowid FROM chained_rows);

3. Delete the chained rows from table.

DELETE FROM tablename WHERE rowid in (SELECT head_rowid FROM chained_rows);

4. Insert the rows back into the table.

INSERT INTO SELECT * FROM chained_temp;

5. Drop the temp table.

Drop table chained_temp;