具体可参照：http://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm#CNCPT1053
首先看下产生行链接以及行迁移的原因。
-
The row is too large to fit into one data block when it is first inserted.
行太大，一个数据块装不下
-
In row migration, Oracle Database moves the entire row to a new data block, assuming the row can fit in a new block. The original row piece of a migrated row contains a pointer or "forwarding address" to the new block containing the migrated row. The rowid of a migrated row does not change.
开始行的资料单个数据块可以装下，但是更新后的资料过大，发生行链接
-
A row has more than 255 columns.
Oracle Database can only store 255 columns in a row piece. Thus, if you insert a row into a table that has 1000 columns, then the database creates 4 row pieces, typically chained over multiple blocks.
列大于255个。
再看数据库中块的大小，可估算到一个块可存多少字节数据。
SQL> conn / as sysdba
Connected.
建立表格t1,其行资料略大于一个块8192，一个数据块装不下。
SQL> create table t1(c1 varchar2(4000),c2 varchar2(4000));
Table created.
SQL> insert into t1 values(lpad('Z',4000,'A'),lpad('Z',4000,'A'));
1 row created.
SQL> commit;
Commit complete.
SQL> @/u01/app/oracle/11.2.0/db_1/rdbms/admin/utlchain.sql
Table created.
分析表t1
SQL> ANALYZE TABLE t1 LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows where table_name='T1';
COUNT(*)
----------
1
我们再建立一表t2,其块大小为32k,这样，一行资料一个块就能装下了
SQL> alter system set db_32k_cache_size=16M;
System altered.
SQL> create tablespace tbs_32 blocksize 32K datafile '/u01/app/oracle/oradata/orcl/tbs_32.dbf' size 100M;
Tablespace created.
SQL> truncate table chained_rows ;
Table truncated.
SQL> ANALYZE TABLE t2 LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows where table_name='T2';
COUNT(*)
----------
0
行链接主要是由于数据库的db_block_size不够大，对于一些大的字段没法在一个block中存储下，从而产生了行链接。对于行链接我们除了增大db_block_size之外没有别的任何办法去避免,因此行链接的产生几乎是不可避免的，也没有太多可以调整的地方。行迁移则主要是由于更新表的时候，由于表的pctfree参数设置太小，导致block中没有足够的空间去容纳更新后的记录，从而产生了行迁移。
SQL> truncate table t1;
Table truncated.
SQL> insert into t1 values('x1','x2');
1 row created.
SQL> insert into t1 values(lpad('Z',2000,'A'),lpad('Z',2000,'A'));
1 row created.
SQL> commit;
Commit complete.
SQL> TRUNCATE TABLE chained_rows;
Table truncated.
SQL> ANALYZE TABLE t1 LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows where table_name='T1';
COUNT(*)
----------
0
SQL> update t1 set c1=lpad('Z',3000,'A'),c2=lpad('Z',3000,'A') where c1='x1';
1 row updated.
SQL> commit;
Commit complete.
SQL> TRUNCATE TABLE chained_rows;
Table truncated.
SQL> ANALYZE TABLE t1 LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows where table_name='T1';
COUNT(*)
----------
1
如何消除行迁移呢，其实方法很多比较常用的就是重新建立表
SQL> create table t3
2 as
3 select * from t1;
Table created.
SQL> ANALYZE TABLE t3 LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows where table_name='T3';
COUNT(*)
----------
0
再就是move,或者导入，导出都可。
也许有人会问，行链接和行迁移怎么验证时为什么都有chained_rows中，没有分开区分，其实ORACLE对于这不是分开讲的，是一齐讲的。
创建时间：2014-8-29 13:45
修改时间：2014-9-4 13:30
