Row Migration/ Chaining in Oracle

Three basic techniques for resolving row migration/chaining:
• Move the table
• Move individual migrated/chained rows within the table
• Rebuild the table using Data Pump (export/import)

Move the table to another tablespace

alter table <table_name< move tablespace ;

Note: It will cause some index unstable. So please check the alert log file during this operation and rebuild the index

alter index rebuilt

Move Individual Migrated/chained rows within table

1. Script creates a table named CHAINED_ROW

@?/rdbms/admin/utlchn1.sql

2. ANALYZE statement to populate the CHAINED_ROWS table

SQL> analyze table emp list chained rows;

3. Check the table having chained rows

SQL> select count(*) from chained_rows where table_name='EMP';

4. Create a temporary holding table to store the chained rows.

create table temp_emp
as select *
from emp
where rowid in
(select head_rowid from chained_rows where table_name = 'EMP');

5. Delete the migrated/chained rows from the original table.

delete from emp
where rowid in
(select head_rowid from chained_rows where table_name = 'EMP');

6. insert records in the temporary table into the EMP table:

insert into emp select * from temp_emp;

Note: please check any trigger is not configured on that table for insert and delete

EXPDP/IMPDP Datapump
————————————–
Expdp the table
drop the table
impdp the table