How to find and eliminate migrated or chained rows

Step by step example.


How to find and eliminate Migrated and Chained rows
---------------------------------------------------

CONCEPTS:

* A row Migrates when a block was found to have the space available for
  a row which underwent an update that increased its size over and beyond
  its block's available space.

* A Chained row occurs when there is no block which can hold the row after 
  it underwent an update which increased its size beyond the available free 
  space in its hosting block. The solution is to split the row over several 
  blocks.          

CAUSES and EFFECTS:

* Causes for migrated and chained rows:  Inserts, updates and deletes over 
  periods of time

* Results from migrated and chained rows:  Degraded response for queries.


SOLUTION:

1)  Analyze the table:

    To prevent an ORA-1495 (specified chained row table not found), run the 
    $ORACLE_HOME/rdbms/admin/utlchain.sql script.
   
    TRUNCATE TABLE CHAINED_ROWS;
    ANALYZE TABLE <table name> LIST CHAINED ROWS;

2)  List the Migrated or Chained rows. 
     
    From SQL*Plus:

    col owner_name format a10
    col table_name format a20
    col head_rowid format a20

    select owner_name, table_name, head_rowid from chained_rows;

3)  You can now eliminate the Migrated or Chained rows by Create Table 
    as Select (CTAS), exporting and then importing the table or by following 
    the next steps:

    A) Create an empty copy of the table that has the Migrated or Chained rows.

       CREATE TABLE <temporary table name> AS
        SELECT * FROM <table name> WHERE ROWID IN
         (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME='<table name'>');

    B) Now delete the Migrated and Chained rows from the table.  
 
       DELETE FROM <table name> WHERE ROWID IN
        (SELECT HEAD_ROWID FROM CHAINED_ROWS 
         WHERE TABLE_NAME='<table name>');

    C) Insert the rows back to the table.

       INSERT INTO <table name> SELECT * FROM <temporary table name>;

    Truncate the chained_rows table and drop the temporary table.


Alternatively, you can move the table to a tablespace if the row cannot fit in the block and you need a tablespace with a larger block size:
   alter table <table_name> move <tablespace>;
   
Note: 
Check the SQL Reference guide for your release, for details on the 'alter table..move..' command.

Note:- Try everything at your own risk, if any thing happens wrong with your database so blogger will not be responsible.