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