Oracle新增字段导致行迁移,oracle11g_如何模拟产生行链接或行迁移chained_rows

---查看块大小为8192byte

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_size                        integer     8192

---创建一个表，记录最大大小超过8192byte,则可以产生行迁链或行迁移

SQL> create table t_row_chain(a varchar2(4000),b varchar2(3000),c varchar2(4000));

Table created

SQL> insert into t_row_chain values('x','y','z');

1 row inserted

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats(user,'t_row_chain',cascade=>true);

PL/SQL procedure successfully completed

SQL> select table_name,chain_cnt from user_tables where table_name='T_ROW_CHAIN';

TABLE_NAME                      CHAIN_CNT

------------------------------ ----------

T_ROW_CHAIN                             0

SQL> update t_row_chain set a=rpad('a',4000,'c');

1 row updated

SQL> commit;

Commit complete

SQL> select length(a) from t_row_chain;

LENGTH(A)

----------

4000

SQL> update t_row_chain set b=rpad('a',3000,'c');

1 row updated

SQL> update t_row_chain set c=rpad('a',4000,'c');

1 row updated

SQL> commit;

Commit complete

--用dbms_stats不能查出行链接或行迁移

SQL>  exec dbms_stats.gather_table_stats(user,'t_row_chain',cascade=>true);

PL/SQL procedure successfully completed

SQL> select table_name,chain_cnt from user_tables where table_name='T_ROW_CHAIN';

TABLE_NAME                      CHAIN_CNT

------------------------------ ----------

T_ROW_CHAIN                             0

SQL> analyze table t_row_chain list chained rows;

analyze table t_row_chain list chained rows

ORA-01495: specified chain row table not found

--先创建存储行链接或行迁移的表

SQL> @D:\oracle11g_64bit\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlchain.sql

Table created

SQL> analyze table t_row_chain list chained rows;

Table analyzed

---行链接产生了

SQL> select * from chained_rows;

OWNER_NAME                     TABLE_NAME                     CLUSTER_NAME                   PARTITION_NAME                 SUBPARTITION_NAME              HEAD_ROWID         ANALYZE_TIMESTAMP

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ -----------------

TBL_BCK                        T_ROW_CHAIN                                                                                  N/A                            AAASD5AAGAAAA4jAAA 2013-05-21 2224