Detecting Migration and Chaining
There three ways in order to find if your database’s blocks are chained or migrated.
The first way is to create the chain_rows tables using the utlchain.sql script under the $ORACLE_HOME/rdbms/admin or call it @?/rdbms/admin/utlchain.sql. After that analyze the table using:
Analyze table xyz list chained rows;
The second way is to detected migrated or chained rows by checking the “table fetch continue row” statistic in V$SYSSTAT.
The third way is to analyze the table first and after that to use the following query
Select num_rows,chain_cnt,
(chain_cnt * 100)/num_rows as " Chained Percentage"
From dba_tables
where table_name=’XYZ’
Eliminating Migrated Rows
Export – Import
Export the table.
Drop or truncate the table.
Import the table.
Move table command
Alter table xyz move
Rebuild xyz’s tables.
Copying migrated rows
Find migraded rows using.
Copy migrated rows to new table.
Delete migrayed rows from original table.
Copy rows from new table to original table;
