行迁移测试实验 【原创】

行迁移测试实验：

1、创建表t1，设置pctfree为0
SQL>create table t1(id number,name varchar2(20)) pctfree 0 tablespace tbs01;

2、插入数据
SQL>insert into t1 select rownum,'AAAAAAA' from dba_objects;
SQL>commit;

3、查看数据的分布情况
SQL>select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid),id from t1 where rownum <=10;

4、更新一行
SQL>select * from t1 where id=956;
SQL>update t1 set name='AAAAAAAAAAAAAAAAAAA' where id=956;
SQL>commit;

5、查看该行的位置是否发生变化
select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid),id from t1 where id=956;

6、执行utlchain.sql，创建chained_rows表
@ ?/rdbms/admin/utlchain.sql

7、分析表t1
SQL>ANALYZE TABLE t1 LIST CHAINED ROWS INTO CHAINED_ROWS;

8、查询分析结果
SQL> SELECT owner_name,                                                                       
  2         table_name,                                                                       
  3         count(head_rowid) row_count                                                       
  4    FROM chained_rows                                                                      
  5  GROUP BY owner_name,table_name;

OWNER_NAME                     TABLE_NAME                      ROW_COUNT
------------------------------ ------------------------------ ----------
LYX                            T1                                      1

SQL> SELECT owner_name,                                                                       
  2         table_name,                                                                       
  3         head_rowid                                                       
  4    FROM chained_rows;

OWNER_NAME                     TABLE_NAME                     HEAD_ROWID
------------------------------ ------------------------------ ------------------
LYX                            T1                             AAACX/AAEAAAAAMAAA

SQL> select * from t1 where rowid='AAACX/AAEAAAAAMAAA';

        ID NAME
---------- --------------------
       956 AAAAAAAAAAAAAAAAAAA

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9472111/viewspace-752208/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9472111/viewspace-752208/