[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

--//空間回收的問題就不修復了.

[20190129]塊內重整3.txt

相關文章