诊断和解决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 字段时几乎不可能避免。
