Friday, March 17, 2006

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;

    1. 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;

    1. delete migrated rows from original table

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

    1. copy rows from new table to original table
insert into table_name select * from migrated_rows;