oracle优化--表优化（行迁移产生实验）

1、建立实验表 create table t_pctfree     (     id number,     name varchar2(2000),     name1 varchar2(2000),     name2 varchar2(2000),     name3 varchar2(2000),     name4 varchar2(2000)     )    tablespace users    pctfree 10; Table created. 2、添加数据，先只写id，其他值为null SQL>insert into t_pctfree(id) values(2); 1 row created. SQL> commit; Commit complete. 3、使用dump查看数据块，确定id为2的数据只存在于一个块中 SQL> select dbms_rowid.rowid_block_number(rowid) block_id from t_pctfree where id=2;   BLOCK_ID ----------      47547 SQL> alter system dump datafile 5 block 51031; System altered. SQL> oradebug setmypid; Statement processed. SQL> oradebug tracefile_name; /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc [oracle@localhost trace]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc tab 0, row 0, @0x1f92 tl: 2 fb: --HDFL-- lb: 0x2  tab 0, row 1, @0x1f8c tl: 6 fb: --H-FL-- lb: 0x0  cc: 1 col  0: [ 2]  c1 03 tab 0, row 2, @0x1f86 SQL> var n number; SQL> exec dbms_stats.convert_raw_value('c103',:n); PL/SQL procedure successfully completed. SQL> print :n N ---------- 2 4、修改id为2的数据，使当前块剩余空间容纳不下修改后的数据 SQL> update scott.t_pctfree set name=dbms_random.string('u', 2000) where id=2; 1 row updated. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. . 5、查看修改后的块状态 SQL> select dbms_rowid.rowid_block_number(rowid) block_id from t_pctfree where id=2;   BLOCK_ID ----------      47547 SQL> alter system dump datafile 5 block 51031; System altered. SQL> oradebug setmypid; Statement processed. SQL> oradebug tracefile_name; /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc [oracle@localhost trace]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc tab 0, row 1, @0xdec tl: 9 fb: --H----- lb: 0x1  cc: 0 nrid:  0x0140c75b.0 6、发现已产生行迁移，源块只留下了迁移块的地址，通过nrid查看迁移目标块信息 SQL> select dbms_utility.DATA_BLOCK_ADDRESS_FILE(to_number('0140c75b', 'xxxxxxxxxx')) file#,dbms_utility.DATA_BLOCK_ADDRESS_BLOCK(to_number('0140c75b','xxxxxxxxxx')) block# from dual;      FILE#     BLOCK# ---------- ---------- 5 51035 SQL> alter system dump datafile 5 block 51035; System altered. SQL> oradebug setmypid; Statement processed. SQL> oradebug tracefile_name; /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc [oracle@localhost trace]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc tab 0, row 0, @0x17a1 tl: 2015 fb: ----FL-- lb: 0x1  cc: 2 hrid: 0x0140b9bb.1 col  0: [ 2]  c1 03 col  1: [2000]  5a 52 57 58 53 54 45 4a 50 4e 56 43 4c 55 4e 4e 4d 47 59 49 51 50 44 41 41  4b 4d 47 56 52 49 5a 51 55 47 54 54 5a 51 41 49 5a 55 57 43 58 46 42 54 55 7、可以看到迁移目标块只有hrid，没有nrid，说明只出现了行迁移，没有出现行链接