诊断和解决row chained and migrated

Listing Chained Rows of  Tables and Clusters

1.execute the UTLCHAIN.SQL or  UTLCHN1.SQL  script.
脚本位于：@oracle_home\rdbms\admin\
该脚本内容：
rem
rem $Header: utlchain.sql 07-may-96.19:40:01 sbasu Exp $
rem
Rem Copyright (c) 1990, 1995, 1996, 1998 by Oracle Corporation
Rem NAME
REM    UTLCHAIN.SQL
Rem  FUNCTION
Rem    Creates the default table for storing the output of the
Rem    analyze list chained rows command
Rem  NOTES
Rem  MODIFIED
Rem     syeung     06/17/98  - add subpartition_name                          
Rem     mmonajje   05/21/96 -  Replace timestamp col name with analyze_timestam
Rem     sbasu      05/07/96 -  Remove echo setting
Rem     ssamu      08/14/95 -  merge PTI with Objects
Rem     ssamu      07/24/95 -  add field for partition name
Rem     glumpkin   10/19/92 -  Renamed from CHAINROW.SQL
Rem     ggatlin    03/09/92 -  add set echo on
Rem     rlim       04/29/91 -         change char to varchar2
Rem   Klein      01/10/91 - add owner name for chained rows
Rem   Klein      12/04/90 - Creation
Rem

create table CHAINED_ROWS (
  owner_name         varchar2(30),
  table_name         varchar2(30),
  cluster_name       varchar2(30),
  partition_name     varchar2(30),
  subpartition_name  varchar2(30),
  head_rowid         rowid,
  analyze_timestamp  date
);

2、执行分析
ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE order_hist LIST CHAINED ROWS;

3.查询分析结果。
SELECT *
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';

OWNER_NAME  TABLE_NAME  CLUST... HEAD_ROWID          TIMESTAMP
----------  ----------  -----... ------------------  ---------
SCOTT       ORDER_HIST       ... AAAAluAAHAAAAA1AAA  04-MAR-96
SCOTT       ORDER_HIST       ... AAAAluAAHAAAAA1AAB  04-MAR-96
SCOTT       ORDER_HIST       ... AAAAluAAHAAAAA1AAC  04-MAR-96

The output lists all rows that are either migrated or chained.

4.创建一张中介表，临时存储 migrated or chained 的行数据
Create an intermediate table with the same columns as the existing table to hold
the migrated and chained rows:

CREATE TABLE int_order_hist
   AS SELECT *
      FROM order_hist
      WHERE ROWID IN
         (SELECT HEAD_ROWID
            FROM CHAINED_ROWS
            WHERE TABLE_NAME = 'ORDER_HIST');

5. 将原表中的migrated and chained rows 删除
Delete the migrated and chained rows from the existing table:

DELETE FROM order_hist
   WHERE ROWID IN
      (SELECT HEAD_ROWID
         FROM CHAINED_ROWS
         WHERE TABLE_NAME = 'ORDER_HIST');

6.将中介表中的行再次插入原表。
 Insert the rows of the intermedia te table into the existing table:

INSERT INTO order_hist
   SELECT *
   FROM int_order_hist;

7.干掉中介表.
Drop the intermediate table:

DROP TABLE int_order_history;

8.删除掉chained_rows表中信息。
Delete the information collected in step 1 from the output table:

DELETE FROM CHAINED_ROWS
   WHERE TABLE_NAME = 'ORDER_HIST';

--------如果要想彻底解决chained rows ，需要增大data block size 。
row chained 在大多数情况下太可能避免，当表有long or large  CHAR or  VARCHAR2 字段时几乎不可能避免。