一次行迁移记录的解决方案

一、查找出有迁移记录的表（解决迁移记录后还是能查出此表，在处理的时候不可以根据这个来判断）
SELECT
    owner                               owner
  , table_name                          table_name
  , ''                                  partition_name
  , num_rows                            num_rows
  , ROUND((chain_cnt/num_rows)*100, 2) || '%' pct_chained_rows
  , avg_row_len                         avg_row_length
FROM
    (select
         owner
       , table_name
       , chain_cnt
       , num_rows
       , avg_row_len
     from
         sys.dba_tables
     where
           chain_cnt is not null
       and num_rows is not null
       and chain_cnt > 0
       and num_rows > 0
       and owner != 'SYS') 
UNION ALL
SELECT
    table_owner                         owner
  , table_name                          table_name
  , partition_name                      partition_name
  , num_rows                            num_rows
  , '<div align="right">' || ROUND((chain_cnt/num_rows)*100, 2) || '%</div>' pct_chained_rows
  , avg_row_len                         avg_row_length
FROM
    (select
         table_owner
       , table_name
       , partition_name
       , chain_cnt
       , num_rows
       , avg_row_len
     from
         sys.dba_tab_partitions
     where
           chain_cnt is not null
       and num_rows is not null
       and chain_cnt > 0
       and num_rows > 0
       and table_owner != 'SYS') b
WHERE
    (chain_cnt/num_rows)*100 > 10;

 

------------------------------------------------------
二、创建记录表
SQL> @/u01/app/oracle/product/10.1/rdbms/admin/utlchain.sql
三、记录发生迁移记录，注意一定要查是否有关联关系，如果有则先禁用后启用
analyze table WFPROCESSINST  list chained rows into chained_rows;
select count(*) from chained_rows;4934
select * from chained_rows;

select index_name,index_type,table_name from user_indexes where table_name='WFPROCESSINST';

select  constraint_name,constraint_type,table_name from user_constraints d where d.r_constraint_name

in(select  constraint_name from user_constraints where table_name='WFPROCESSINST')
-------------有迁移记录的行 consistent gets 不是1---------------------------
SQL> set autotrace on
SQL> select *  from WFPROCESSINST where rowid='AAANLvAAIAABJBlAAD';
7  consistent gets

-------------对迁移记录的处理
create  table  terminal_temp  as

select  *  from  WFPROCESSINST  where  rowid  in(select  head_rowid  from  chained_rows where  table_name  =  'WFPROCESSINST'); 

copy from tssa/tssa2006@sxtsioms to tssa/tssa2006@sxtsioms create terminal_temp using select  *  from  WFPROCESSINST  where  rowid  in(select  head_rowid  from  chained_rows where  table_name  =  'WFPROCESSINST');
-------------------------
select count(*)from WFPROCESSINST;31493

select count(*)from terminal_temp;4934
      
       delete WFPROCESSINST

 where rowid in
     
       (select head_rowid
       
          from chained_rows
       
         where table_name = 'WFPROCESSINST');
        
         commit;
   ---------------------     
         insert  into  WFPROCESSINST select  *  from  terminal_temp;
copy from tssa/tssa2006@sxtsioms to tssa/tssa2006@sxtsioms Append  WFPROCESSINST using select  *  from terminal_temp; 

--------查看处理结果
SQL> set autotrace on
SQL> select *  from WFPROCESSINST where rowid='AAANLvAAIAABJBlAAD';
1 consistent gets

truncate table chained_rows;
analyze table WFPROCESSINST list chained rows into chained_rows
select count(*) from chained_rows;---0