实验：行迁移与分析语句 row migration and analyze statements-优快云博客

步骤：

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