Row migration/chaining solution in Oracle

 

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:
———————
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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.