步骤：
1 建立存储chained_row信息的表
2 建立一个将要发生row migration 的表
3 更新表，使其发生row migration，通过analyze语句查看。
环境：
所用数据库 block_size=8192 PCTFREE=10
根据block_size，PCTFREE的不同发生行迁移的条件会不同
oracle 10.2.0.5.0
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
x86_64
具体过程：
1 建立存储chained_row信息的表
| @?/rdbms/admin/utlchain.sql --或 @?/rdbms/admin/utlchn1.sql |
--其中，utlchain.sql 如下
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
);
utlchain.sql和utlchn1.sql 的区别在于 head_rowid 字段utlchain.sql为rowid,utlchn1.sql 为urowid
两者的区别 http://hi.baidu.com/bystander1983/blog/item/727e2b12658f8b085aaf53a4.html
2 建立一个用于发生row migration 的表，插入数据
| z@test10g> drop table t; Table dropped. z@test10g> create table t 2 ( a int,b varchar2(4000)) 3 / Table created. z@test10g> insert into t (a) values (1); 1 row created. z@test10g> insert into t (a) values (2); 1 row created. z@test10g> insert into t (a) values (3); 1 row created. z@test10g> insert into t (a) values (4); 1 row created. z@test10g> commit; Commit complete. |
3 更新表，使其发生row migration。
z@test10g> update t set b=rpad('*',4000,'*') where a=1;
1 row updated.
z@test10g> update t set b=rpad('*',4000,'*') where a=2;
1 row updated.
--由于block_size=8192，此时 a=3 的行发生行迁移
z@test10g> update t set b=rpad('*',4000,'*') where a=3;
1 row updated.
z@test10g> commit;
Commit complete.通过analyze语句查看
z@test10g> ANALYZE TABLE t LIST CHAINED ROWS INTO CHAINED_ROWS;
Table analyzed.
z@test10g> select * from CHAINED_ROWS;
OWNER_NAME TABLE_NAME CLUSTER_NA PARTITION_ SUBPARTITI HEAD_ROWID ANALYZE_T
---------- ---------- ---------- ---------- ---------- ------------------ ---------
z T N/A AAADT1AABAAAGEKAAC 30-NOV-10
z@test10g> select rowid,a from t;
ROWID A
------------------ ----------
AAADT1AABAAAGEKAAA 1
AAADT1AABAAAGEKAAB 2
AAADT1AABAAAGEKAAD 4
AAADT1AABAAAGEKAAC 3此时再更新 a=4 的行，发生行迁移
z@test10g> update t set b=rpad('*',4000,'*') where a=4;
1 row updated.
z@test10g> commit;,
Commit complete.
z@test10g> ANALYZE TABLE t LIST CHAINED ROWS INTO CHAINED_ROWS;
Table analyzed.
z@test10g> select * from CHAINED_ROWS;
OWNER_NAME TABLE_NAME CLUSTER_NA PARTITION_ SUBPARTITI HEAD_ROWID ANALYZE_T
---------- ---------- ---------- ---------- ---------- ------------------ ---------
z T N/A AAADT1AABAAAGEKAAC 30-NOV-10
z T N/A AAADT1AABAAAGEKAAC 30-NOV-10
z T N/A AAADT1AABAAAGEKAAD 30-NOV-10
记于 2010年11月30日