[20190129]块内重整3.txt

来源：这里教程网时间：2026-03-03 12:55:07 作者：

[20190129]块内重整3.txt --//链接http://blog.itpub.net/267265/viewspace-2287110/,与别人交流,提到一个情况,kdbr行目录的更新并不一定发生在 --//当到达顶部时,会出现一次块内重整(也有叫块内重组).通过例子说明: 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> create table empy as select * from emp; Table created. SCOTT@book> select rowid,empy.* from empy where rownum=1; ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- AAAWQiAAEAAAAITAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 SCOTT@book> @ rowid AAAWQiAAEAAAAITAAA OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 91170 4 531 0 0x1000213 4,531 alter system dump datafile 4 block 531 ; 2.测试: --//先删除1条记录.然后修改1条记录看看. SCOTT@book> delete from empy where EMPNO=7499; 1 row deleted. SCOTT@book> commit ; Commit complete. SCOTT@book> alter system checkpoint ; System altered. --//通过bbed观察: BBED> set dba 4,531 DBA 0x01000213 (16777747 4,531) BBED> p kdbr sb2 kdbr[0] @142 8026 sb2 kdbr[1] @144 7983 sb2 kdbr[2] @146 7940 sb2 kdbr[3] @148 7899 sb2 kdbr[4] @150 7854 sb2 kdbr[5] @152 7813 sb2 kdbr[6] @154 7772 sb2 kdbr[7] @156 7732 sb2 kdbr[8] @158 7694 sb2 kdbr[9] @160 7651 sb2 kdbr[10] @162 7613 sb2 kdbr[11] @164 7575 sb2 kdbr[12] @166 7536 sb2 kdbr[13] @168 7497 SCOTT@book> update empy set JOB=lower(JOB) where EMPNO=7369; 1 row updated. SCOTT@book> commit ; Commit complete. SCOTT@book> alter system checkpoint ; System altered. BBED> p kdbr sb2 kdbr[0] @142 8026 sb2 kdbr[1] @144 7983 sb2 kdbr[2] @146 7940 sb2 kdbr[3] @148 7899 sb2 kdbr[4] @150 7854 sb2 kdbr[5] @152 7813 sb2 kdbr[6] @154 7772 sb2 kdbr[7] @156 7732 sb2 kdbr[8] @158 7694 sb2 kdbr[9] @160 7651 sb2 kdbr[10] @162 7613 sb2 kdbr[11] @164 7575 sb2 kdbr[12] @166 7536 sb2 kdbr[13] @168 7497 --//行目录没有变化.因为修改长度没有变化. SCOTT@book> update empy set JOB=upper(JOB)||'A' where EMPNO=7369; 1 row updated. SCOTT@book> commit ; Commit complete. SCOTT@book> alter system checkpoint ; System altered. --//长度发生变化,kdbr[0]的指向的偏移现在最小. BBED> set dba 4,531 DBA 0x01000213 (16777747 4,531) BBED> p kdbr sb2 kdbr[0] @142 7458 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ sb2 kdbr[1] @144 -1 sb2 kdbr[2] @146 7940 sb2 kdbr[3] @148 7899 sb2 kdbr[4] @150 7854 sb2 kdbr[5] @152 7813 sb2 kdbr[6] @154 7772 sb2 kdbr[7] @156 7732 sb2 kdbr[8] @158 7694 sb2 kdbr[9] @160 7651 sb2 kdbr[10] @162 7613 sb2 kdbr[11] @164 7575 sb2 kdbr[12] @166 7536 sb2 kdbr[13] @168 7497 BBED> p kdbh struct kdbh, 14 bytes @124 ub1 kdbhflag @124 0x00 (NONE) sb1 kdbhntab @125 1 sb2 kdbhnrow @126 14 sb2 kdbhfrre @128 1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ sb2 kdbhfsbo @130 46 sb2 kdbhfseo @132 7458 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ sb2 kdbhavsp @134 7493 sb2 kdbhtosp @136 7493 --//可以发现kdbr[1]=-1,注意看下划线的值.这样可以重用kdbr[1]的记录,理论讲只要没有覆盖恢复还可可行的. --//感觉这个应该是行目录整理,oracle总是把一些事情分散完成,而不是一次完成. 3.尝试恢复看看. BBED> set count 20 COUNT 20 BBED> set offset 7458 OFFSET 7458 BBED> find /x 3c curr File: /mnt/ramdisk/book/users01.dbf (4) Block: 531 Offsets: 8107 to 8126 Dba:0x01000213 -------------------------------------------------------------------------------------------------- 3c020803 c24b6405 414c4c45 4e085341 4c45534d <64 bytes per line> BBED> assign offset 8107=0x2c; ub1 rowdata[0] @8107 0x2c BBED> x /rnccntnn offset 8107 rowdata[525] @8107 ------------ flag@8107: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8108: 0x02 cols@8109: 8 col 0[3] @8110: 7499 col 1[5] @8114: ALLEN col 2[8] @8120: SALESMAN col 3[3] @8129: 7698 col 4[7] @8133: 1981-02-20 00:00:00 col 5[2] @8141: 1600 col 6[2] @8144: 300 col 7[2] @8147: 30 BBED> map File: /mnt/ramdisk/book/users01.dbf (4) Block: 531 Dba:0x01000213 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 96 bytes @20 struct kdbh, 14 bytes @124 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ struct kdbt[1], 4 bytes @138 sb2 kdbr[14] @142 ub1 freespace[7412] @170 ub1 rowdata[606] @7582 ub4 tailchk @8188 --//kdbh偏移在124,有3个ITL槽. --//8107 - 124 = 7983,行目录保存的是相对偏移. BBED> assign kdbr[1] =7983; sb2 kdbr[0] @144 7983 BBED> sum apply Check value for File 4, Block 531: current = 0x2e9a, required = 0x2e9a BBED> verify DBVERIFY - Verification starting FILE = /mnt/ramdisk/book/users01.dbf BLOCK = 531 Block Checking: DBA = 16777747, Block Type = KTB-managed data block data header at 0x7f4d1bed427c kdbchk: row locked by non-existent transaction table=0 slot=1 lockid=2 ktbbhitc=3 Block 531 failed with check code 6101 --//全表扫描已经ok了. SCOTT@book> select * from empy; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERKA 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 14 rows selected. ---//继续修改看看: BBED> assign kdbh.kdbhfrre=-1; sb2 kdbhfrre @128 -1 BBED> sum apply Check value for File 4, Block 531: current = 0xd164, required = 0xd164 BBED> verify DBVERIFY - Verification starting FILE = /mnt/ramdisk/book/users01.dbf BLOCK = 531 Block Checking: DBA = 16777747, Block Type = KTB-managed data block data header at 0x1fe8e7c kdbchk: row locked by non-existent transaction table=0 slot=1 lockid=2 ktbbhitc=3 Block 531 failed with check code 6101 BBED> x /rnccntnn *kdbr[1] rowdata[525] @8107 ------------ flag@8107: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8108: 0x02 cols@8109: 8 col 0[3] @8110: 7499 col 1[5] @8114: ALLEN col 2[8] @8120: SALESMAN col 3[3] @8129: 7698 col 4[7] @8133: 1981-02-20 00:00:00 col 5[2] @8141: 1600 col 6[2] @8144: 300 col 7[2] @8147: 30 BBED> assign offset 8108=0x0; ub1 rowdata[0] @8108 0x00 BBED> sum apply Check value for File 4, Block 531: current = 0xd166, required = 0xd166 BBED> verify DBVERIFY - Verification starting FILE = /mnt/ramdisk/book/users01.dbf BLOCK = 531 Block Checking: DBA = 16777747, Block Type = KTB-managed data block data header at 0x1fe8e7c kdbchk: the amount of space used is not equal to block size used=614 fsc=0 avsp=7493 dtl=8064 Block 531 failed with check code 6110 --//空间回收的问题就不修复了.