Fixing Migrated/Chained Rows

Definitions:

Row Chaining: A row is too large to fit into a single database block. For example, if you use 8 KB block size for your database, and you need to insert a row of 16 KB into it, Oracle will use 2 blocks and store the row in pieces.
Row Chaining is often unavoidable with tables have (LONG, large CHAR, VARCHAR2) columns.

Row Migration: A row will migrate when an update to that row would cause it to not fit on the block, the row will move to a new block leaving a link(forwarding address) in its original block pointing to the new block location.

The Harm of Migrated/Chained Rows:
Migrated/Chained rows can cause bad database performance by affecting index reads and full table scans.

How to fix:

You can use one of the following solutions the first one is 2 steps and the other is 10 steps :-)
This will depends on the number of chained rows and the downtime you can take for applying the fix:

Solution #1: 2 steps
========


Note: This solution will take longer downtime on the table. (preferred when chained rows are much)

1- Rebuild the table having chained rows:
SQL> alter table TEST move;
         -->This will invalidate all indexes that are associated with the table TEST.

Note: You can increase the PCTFREE within the move command to reduce row chaining possibility in the future:

      SQL> alter table TEST move pctfree 30;
               -->Note: PCTFREE default value is 10

2- Rebuild ALL Indexes associated with the table:
SQL> SELECT 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild online ;'FROM DBA_INDEXES where TABLE_NAME='TEST';

Done.

Solution #2: 10 Steps
========

1- Create a table contains the chained rows result using this script:

$ORACLE_HOME/rdbms/admin/utlchain.sql

2- Collect information about migrated and chained rows:

SQL> ANALYZE TABLE TEST LIST CHAINED ROWS;

3- Query the output table:

SELECT * FROM CHAINED_ROWS WHERE TABLE_NAME = 'TEST';

OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID TIMESTAMP
---------- ---------- -----... ------------------ ---------
SCOTT TEST ... AAAVdkAATAAAfpfAA1 25-JUL-11
SCOTT TEST ... AAAVdkAATAAAfpfAA9 25-JUL-11

If there are many migrated/chained rows you can go ahead through following steps:

4- Create an intermediate table holding chained rows with the same structure as org table:

SQL> CREATE TABLE int_TEST AS SELECT * FROM TEST WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'TEST');

Note: "Create Table As" statement will fail if the original table includes LONG datatype

5- Delete the migrated/chained rows from the original table:

SQL> DELETE FROM TEST
WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'TEST');

If it returned back ORA-02292: integrity constraint (xxx)violated, disable that referential constraint and try again.

6-Insert the rows back from the intermediate table into the original table:

SQL> INSERT INTO TEST SELECT * FROM int_TEST;

7-Drop the intermediate table:

SQL> DROP TABLE int_TEST;

8-Delete the information collected in step 1 from the output table:

SQL> DELETE FROM CHAINED_ROWS WHERE TABLE_NAME = 'TEST';

9- Use the ANALYZE statement again:

SQL> ANALYZE TABLE TEST LIST CHAINED ROWS;

10- Check for chained rows: -Should be 0-

SQL> SELECT * FROM CHAINED_ROWS WHERE TABLE_NAME = 'TEST';

no rows selected

Done.