[20190129]簡單探究cluster table(補充)4.txt

--//在連結http://blog.itpub.net/267265/viewspace-2286968/=>[20181229]簡單探究cluster table(補充)3.txt,

--//裡面提到我看到的一個現象,在刪除全部關聯的cluster 鍵值後,mref部分(mref=0),並且hrid的offset佔了mref

--//的原來的位置.nrid也做了移動.col 0的位置沒有變動.

--//實際上上面是bbed顯示上的一個bug,學習不夠仔細,還是透過例子說明情況:

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

2.建立測試環境:

create cluster deptx_cluster (deptno number(2)) size 800;

--//加入引數size 800

create table deptx

(

deptno number(2) ,

dname varchar2(14 byte),

loc varchar2(13 byte)

) cluster deptx_cluster (deptno);

alter table deptx add constraint pk_deptx primary key (deptno);

create table empx

(

empno number(4) ,

ename varchar2(10 byte),

job varchar2(9 byte),

mgr number(4),

hiredate date,

sal number(7,2),

comm number(7,2),

deptno number(2)

) cluster deptx_cluster (deptno);

--//取消主外來鍵約束.

alter table empx add constraint pk_empx primary key (empno);

create index i_deptx_cluster_deptno on cluster deptx_cluster;

--//注這裡不能使用unique,否則報ORA-01715: UNIQUE may not be used with a cluster index

insert into deptx select * from dept;

insert into empx select * from emp;

commit;

--//分析略.

SCOTT@book> alter system checkpoint ;

System altered.

3.檢視資料:

SCOTT@book> select rowid,deptx.* from deptx where deptno=20;

ROWID DEPTNO DNAME LOC

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

AAAWQOAAEAAAAIMAAB 20 RESEARCH DALLAS

SCOTT@book> select rowid,empx.* from empx where deptno=20;

ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

AAAWQOAAEAAAAIMAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

AAAWQOAAEAAAAIMAAD 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

AAAWQOAAEAAAAIMAAH 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

AAAWQOAAEAAAAIMAAK 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

AAAWQOAAEAAAAIMAAM 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

SCOTT@book> @ rowid AAAWQOAAEAAAAIMAAB

OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT

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

91150 4 524 1 0x100020C 4,524 alter system dump datafile 4 block 524 ;

BBED> p kdbt[0]

struct kdbt[0], 4 bytes @114

sb2 kdbtoffs @114 0

sb2 kdbtnrow @116 4

BBED> p kdbt[1]

struct kdbt[1], 4 bytes @118

sb2 kdbtoffs @118 4

sb2 kdbtnrow @120 4

BBED> p kdbt[2]

struct kdbt[2], 4 bytes @122

sb2 kdbtoffs @122 8

sb2 kdbtnrow @124 14

BBED> x /rn *kdbr[0]

rowdata[679] @8166

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

flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)

lock@8167: 0x00

cols@8168: 1

kref@8169: 4

mref@8171: 4

hrid@8173:0x0100020c.0

nrid@8179:0x0100020c.0

col 0[2] @8185: 10

BBED> x /rn *kdbr[1]

rowdata[633] @8120

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

flag@8120: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)

lock@8121: 0x00

cols@8122: 1

kref@8123: 6

mref@8125: 6

hrid@8127:0x0100020c.1

nrid@8133:0x0100020c.1

col 0[2] @8139: 20

BBED> dump /v offset 8120 count 22

File: /mnt/ramdisk/book/users01.dbf (4)

Block: 524 Offsets: 8120 to 8141 Dba:0x0100020c

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

ac000106 00060001 00020c00 01010002 0c000102 c115 l ......................

--// ~~~~~!!!! =>~~ 表示 kref,!! 表示mref,佔用2個位元組. intel系列cpu大小頭對調來看.

<32 bytes per line>

--//注意mref的偏移量8125.deptno=20的記錄,deptx 1條記錄,empx 5條記錄.

BBED> x /rcc *kdbr[5]

rowdata[613] @8100

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

flag@8100: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)

lock@8101: 0x02

cols@8102: 2

ckix@8103: 1

col 0[8] @8104: RESEARCH

col 1[6] @8113: DALLAS

--//ckix=1,表示cluster 鍵值取自 *kdbr[1].也就是deptno=20.

BBED> x /rcc *kdbr[4]

rowdata[655] @8142

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

flag@8142: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)

lock@8143: 0x02

cols@8144: 2

col 0[10] @8146: ACCOUNTING

col 1[8] @8157: NEW YORK

--//ckix沒有顯示,實際上ckix=0,表示cluster 鍵值取自 *kdbr[0].也就是deptno=10.

--//實際上如果你仔細看上面x /rcc *kdbr[5]的cols以及ckix的偏移量就可以發現2個緊挨著的,ckix佔1個位元組.

--//而沒有ckix顯示的x /rcc *kdbr[4],cols,col 0的偏移差存在2個位元組.也就是中間的offset=8145對應的值就是ckix.

--//感覺bbed設計上不應該跳過ckix=0的顯示,這樣不科學.

BBED> dump /v offset 8142 count 24

File: /mnt/ramdisk/book/users01.dbf (4)

Block: 524 Offsets: 8142 to 8165 Dba:0x0100020c

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

6c020200 0a414343 4f554e54 494e4708 4e455720 594f524b l l....ACCOUNTING.NEW YORK

~~對應ckix.

<32 bytes per line>

4.刪除deptno=20的記錄看看:

SCOTT@book> delete from empx where deptno=20;

5 rows deleted.

SCOTT@book> delete from deptx where deptno=20;

1 row deleted.

SCOTT@book> commit ;

Commit complete.

SCOTT@book> alter system checkpoint ;

System altered.

BBED> x /rn *kdbr[1]

rowdata[633] @8120

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

flag@8120: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)

lock@8121: 0x00

cols@8122: 1

kref@8123: 6

hrid@8125:0x0100020c.1

nrid@8131:0x0100020c.1

col 0[2] @8139: 20

--//刪除6條記錄後.mref透過bbed無法看到,我把上面的顯示貼到下面便於對比.

BBED> x /rn *kdbr[1]

rowdata[633] @8120

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

flag@8120: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)

lock@8121: 0x00

cols@8122: 1

kref@8123: 6

mref@8125: 6

hrid@8127:0x0100020c.1

nrid@8133:0x0100020c.1

col 0[2] @8139: 20

--//可以發現offset=8125的位置現在顯示的hrid的偏移,從bbed上看刪除全部關聯的cluster鍵值後hrid,nrid整體上移2個位元組.

--//而實際上還是bbed顯示上的一個bug.

BBED> dump /v offset 8120 count 22

File: /mnt/ramdisk/book/users01.dbf (4)

Block: 524 Offsets: 8120 to 8141 Dba:0x0100020c

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

ac000106 00000001 00020c00 01010002 0c000102 c115 l ......................

<32 bytes per line>

--//對比前面的顯示

BBED> dump /v offset 8120 count 22

File: /mnt/ramdisk/book/users01.dbf (4)

Block: 524 Offsets: 8120 to 8141 Dba:0x0100020c

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

ac000106 00060001 00020c00 01010002 0c000102 c115 l ......................

~~~~~!!!! =>~~ 表示 kref,!! 表示mref,佔用2個位元組.

<32 bytes per line>

--//仔細看實際上hrid,nrid並沒有變動.而僅僅是bbed顯示上的一個bug.

--//感覺bbed設計上的問題,應該正常顯示mref=0.而bbed不顯示這個0.

--//可以透過一個簡單的方法驗證,直接修改offset=8125,8126的內容驗證看看.

BBED> assign offset 8125=0x34;

ub1 rowdata[0] @8125 0x34

BBED> assign offset 8126=0x12;

ub1 rowdata[0] @8126 0x12

--//0x1234=4660.

BBED> x /rn *kdbr[1]

rowdata[633] @8120

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

flag@8120: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)

lock@8121: 0x00

cols@8122: 1

kref@8123: 6

mref@8125: 4660

~~~~~~~~~~~~~~~~~~

hrid@8127:0x0100020c.1

nrid@8133:0x0100020c.1

col 0[2] @8139: 20

--//再次說明學習細節很重要,要仔細論證檢視,要敢於懷疑,仔細一想就明白,oracle不可能上移動hrid.nrid

--//這樣對應的塊資料結構使用C語言定義的不對了.

--//如果上移mref實際上對應位置是0x0100.oracle如何知道這個位置是mref還是hrid呢.

5.最後嘗試手工恢復看看.

BBED> p kdbh

struct kdbh, 14 bytes @100

ub1 kdbhflag @100 0x01 (KDBHFFK)

sb1 kdbhntab @101 3

sb2 kdbhnrow @102 22

sb2 kdbhfrre @104 -1

sb2 kdbhfsbo @106 70

sb2 kdbhfseo @108 7387

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

sb2 kdbhavsp @110 7317

sb2 kdbhtosp @112 7518

BBED> p kdbr

sb2 kdbr[0] @126 8066

sb2 kdbr[1] @128 8020

sb2 kdbr[2] @130 7978

sb2 kdbr[3] @132 7938

sb2 kdbr[4] @134 8042

sb2 kdbr[5] @136 8000

sb2 kdbr[6] @138 7960

sb2 kdbr[7] @140 7916

sb2 kdbr[8] @142 7881

sb2 kdbr[9] @144 7840

sb2 kdbr[10] @146 7799

sb2 kdbr[11] @148 7761

sb2 kdbr[12] @150 7718

sb2 kdbr[13] @152 7680

sb2 kdbr[14] @154 7642

sb2 kdbr[15] @156 7605

sb2 kdbr[16] @158 7570

sb2 kdbr[17] @160 7529

sb2 kdbr[18] @162 7494

sb2 kdbr[19] @164 7459

sb2 kdbr[20] @166 7423

sb2 kdbr[21] @168 7387

--//當前最小行目錄的偏移是7387.

BBED> set count 20

COUNT 20

BBED> set offset 7387

OFFSET 7387

--//注意count不要設定太大,避免跳過一些記錄.

BBED> find /x 7c curr

File: /mnt/ramdisk/book/users01.dbf (4)

Block: 524 Offsets: 7523 to 7542 Dba:0x0100020c

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

7c020601 03c25003 04464f52 4407414e 414c5953

<64 bytes per line>

BBED> find

File: /mnt/ramdisk/book/users01.dbf (4)

Block: 524 Offsets: 7594 to 7613 Dba:0x0100020c

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

7c020601 03c24f4d 05414441 4d530543 4c45524b

<64 bytes per line>

BBED> find

File: /mnt/ramdisk/book/users01.dbf (4)

Block: 524 Offsets: 7705 to 7724 Dba:0x0100020c

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

7c020601 03c24e59 0553434f 54540741 4e414c59

<64 bytes per line>

BBED> find

File: /mnt/ramdisk/book/users01.dbf (4)

Block: 524 Offsets: 7861 to 7880 Dba:0x0100020c

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

7c020601 03c24c43 054a4f4e 4553074d 414e4147

<64 bytes per line>

BBED> find

File: /mnt/ramdisk/book/users01.dbf (4)

Block: 524 Offsets: 7981 to 8000 Dba:0x0100020c

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

7c020601 03c24a46 05534d49 54480543 4c45524b

<64 bytes per line>

BBED> find

File: /mnt/ramdisk/book/users01.dbf (4)

Block: 524 Offsets: 8100 to 8119 Dba:0x0100020c

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

7c020201 08524553 45415243 48064441 4c4c4153

<64 bytes per line>

BBED> find

BBED-00212: search string not found

--//記下偏移7523 7594 7705 7861 7981 8100,注意看看是否正確.

assign offset 7523 = 0x6c

assign offset 7594 = 0x6c

assign offset 7705 = 0x6c

assign offset 7861 = 0x6c

assign offset 7981 = 0x6c

assign offset 8100 = 0x6c

BBED> assign offset 8125=0x6

ub1 rowdata[0] @8125 0x06

BBED> assign offset 8126=0x0

ub1 rowdata[0] @8126 0x00

--//如果不修改mref值.報如下錯誤.

BBED> assign offset 8125=0x0

ub1 rowdata[0] @8125 0x00

BBED> sum apply

Check value for File 4, Block 524:

current = 0x8bed, required = 0x8bed

BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/users01.dbf

BLOCK = 524

Block Checking: DBA = 16777740, Block Type = KTB-managed data block

data header at 0x1964c64

kdbchk: key comref count wrong

keyslot=1

Block 524 failed with check code 6121

--//繼續:

assign offset 8125=0x6

assign offset 8126=0x0

BBED> sum apply

Check value for File 4, Block 524:

current = 0x8ded, required = 0x8ded

BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/users01.dbf

BLOCK = 524

Block Checking: DBA = 16777740, Block Type = KTB-managed data block

data header at 0x7fbd9434c264

kdbchk: the amount of space used is not equal to block size

used=771 fsc=177 avsp=7317 dtl=8088

Block 524 failed with check code 6110

--//OK,這個錯誤不修復了.

SCOTT@book> select * from empx where deptno=20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

SCOTT@book> select * from deptx where deptno=20;

no rows selected

--//索引沒有同步.

SCOTT@book> select /*+ full(deptx) */ * from deptx where deptno=20;

DEPTNO DNAME LOC

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

20 RESEARCH DALLAS

SCOTT@book> alter index pk_deptx rebuild online ;

Index altered.

SCOTT@book> select * from deptx where deptno=20;

DEPTNO DNAME LOC

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

20 RESEARCH DALLAS

--//注意empx的索引也要修復.不然:

SCOTT@book> select * from empx;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

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

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

7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

9 rows selected.

SCOTT@book> alter index pk_empx rebuild online;

Index altered.

--//注意一定要加入online引數.

[20180129]簡單探究cluster table(補充)4.txt

相關文章