行的迁移

create table t (x varchar2(20));

begin
  for i in 1..1000 loop
    insert into t values ('H');
  end loop;
  commit;
end;
/

收集对象的统计信息:
analyze table t compute statistics;

查看统计信息:
select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,NUM_ROWS,AVG_ROW_LEN,CHAIN_CNT
from user_tables where table_name='T';

TABLE_NAME     BLOCKS EMPTY_BLOCKS   NUM_ROWS AVG_ROW_LEN  CHAIN_CNT
----------             ----------  ------------ ---------- ----------- ----------
T                        5            3                        1000              5                       0 --没有迁移

更新表产生迁移:
update t set x=rpad('H',20,'A');
commit;

重新收集对象的统计信息:
analyze table t compute statistics;

select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,NUM_ROWS,AVG_ROW_LEN,CHAIN_CNT from user_tables where table_name='T';

TABLE_NAME     BLOCKS EMPTY_BLOCKS   NUM_ROWS AVG_ROW_LEN  CHAIN_CNT
---------- ---------- ------------ ---------- ----------- ----------
T                  13            3       1000          28        659--产生迁移

消除迁移:

1.exp --> drop --> imp
2.alter table t move; -->索引全失效-->alter index xxxx rebuild;
3.找到迁移的行导出，删除，导入
@?/rdbms/admin/utlchain      -->create table chained_rows ();

使用分析命令将产生迁移的行的rowid插入到chained_rows表:
analyze table t list chained rows into chained_rows;

导出发生迁移的行到临时表
create table tmp as select * from t where rowid in (select head_rowid from chained_rows);
删除迁移的行
delete t where rowid in (select head_rowid from chained_rows);
插入迁移的行
insert into t select * from tmp;
删除临时表
drop table tmp purge;

analyze table t compute statistics;

select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,NUM_ROWS,AVG_ROW_LEN,CHAIN_CNT
from user_tables where table_name='T';
TABLE_NAME     BLOCKS EMPTY_BLOCKS   NUM_ROWS AVG_ROW_LEN  CHAIN_CNT
---------- ---------- ------------ ---------- ----------- ----------
T                  13            3       1000          28       0