DETECTING MIGRATION AND CHAINING ROWS

1. ANALYZE TABLE OWNER.TABLE_NAME COMPUTE STATISTICS;
2. SELECT NUM_ROWS,CHAIN_CNT FROM DBA_TABLES WHERE TABLE_NAME=’TABLE_NAME’;

CHAIN_CNT= MIGRATED + CHAINED ROWS.

FOR LIST OF ROWS.

1. ANALYZE TABLE OWNER.TABLE_NAME LIST CHAINED ROWS;
2. SELECT OWNER_NAME,TABLE_NAME,HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME=’TABLE_NAME’;
TO CREATE CHAINED_ROWS TABLE RUN UTLCHAIN.SQL

ELIMINATION MIGRATED ROWS.

1. EXPORT / IMPORT
1. EXPORT THE TABLE
2. DROP OR TRUNCATE THE TABLE
3. IMPORT THE TABLE
2. MOVE TABLE COMMAND
1. ALTER TABLE TABLE_NAME MOVE;
3. COPY MIGRATED ROWS
1. FIND MIGRATED ROWS USING ANALYZE

ANALYZE TALBE TABLE_NAME LIST CHAINED ROWS;

2. COPY MIGRATED ROWS TO NEW TABLE

CREATE TABLE MIGRATED_ROWS AS
SELECT ORIG.*
FROM TABLE_NAME ORIG,CHAINED_ROWS CR
WHERE ORIG.ROWID=CR.ROWID
AND CR.TABLE_NAME=TABLE_NAME;

3. delete migrated rows from original table

delete from table_name
where rowid in
(select head_rowid from chained_rows);

4. copy rows from new table to original table

insert into table_name select * from migrated_rows;