[20181226]簡單探究cluster table.txt

--//簡單探究cluster table.以前也做過,有點生疏了.

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));

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) references deptx

) cluster deptx_cluster (deptno);

alter table empx add constraint constraint_name 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 where deptno=10;

insert into empx select * from emp where deptno=10;

insert into deptx select * from dept where deptno=20;

insert into empx select * from emp where deptno=20;

insert into deptx select * from dept where deptno=30;

insert into empx select * from emp where deptno=30;

insert into deptx select * from dept where deptno=40;

insert into empx select * from emp where deptno=40;

commit;

3.檢視資料:

SCOTT@book> select rowid,deptx.* from deptx;

ROWID DEPTNO DNAME LOC

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

AAAWEfAAEAAAAIrAAA 20 RESEARCH DALLAS

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

AAAWEfAAEAAAAItAAA 30 SALES CHICAGO

AAAWEfAAEAAAAIuAAA 40 OPERATIONS BOSTON

AAAWEfAAEAAAAIvAAA 10 ACCOUNTING NEW YORK

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

ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

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

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

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

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

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

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

SCOTT@book> @ dpc '' ''

PLAN_TABLE_OUTPUT

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

SQL_ID fw7tmp1r74rf4, child number 0

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

select rowid,empx.* from empx where deptno=20

Plan hash value: 1833007843

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS CLUSTER| EMPX | 5 | 190 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | I_DEPTX_CLUSTER_DEPTNO | 1 | | 1 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1 / EMPX@SEL$1

2 - SEL$1 / EMPX@SEL$1

Predicate Information (identified by operation id):

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

2 - access("DEPTNO"=20)

--//可以發現查詢empx表的deptno=20可以利用cluster table的索引.另外你可以注意一個特點cluster table裡面的表rowid可以相同.比如下劃線的內容.

--//實際上這樣設計相關表的查詢都儲存在相同塊中,連線訪問會快許多.

SCOTT@book> @ rowid AAAWEfAAEAAAAIrAAA

OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT

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

90399 4 555 0 0x100022B 4,555 alter system dump datafile 4 block 555 ;

--//轉儲dba=4,555看看.

3.轉儲分析:

SCOTT@book> alter system flush buffer_cache;

System altered.

SCOTT@book> alter system dump datafile 4 block 555 ;

System altered.

Block header dump: 0x0100022b

Object id on Block? Y

seg/obj: 0x1611f csc: 0x03.175f42b6 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x1000228 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0009.01b.00000a44 0x00c00356.0394.31 C--- 0 scn 0x0003.175f42b6

0x02 0x000a.001.00004cfa 0x00c02e94.0ef9.27 --U- 6 fsc 0x0000.175f42cc

bdba: 0x0100022b

data_block_dump,data header at 0x7f286b5f1064

===============

tsiz: 0x1f98

hsiz: 0x28

pbl: 0x7f286b5f1064

76543210

flag=-------K

ntab=3

nrow=7

frre=-1

fsbo=0x28

fseo=0x1eb9

avsp=0x1e91

tosp=0x1e91

0xe:pti[0] nrow=1 offs=0

0x12:pti[1] nrow=1 offs=1

0x16:pti[2] nrow=5 offs=2

--//說明有3個表,cluster表deptx_cluster以及表deptx,empx.行數分別是1,1,5(下面可以看到對於7條記錄).後面offs表示偏移量.

0x1a:pri[0] offs=0x1f82

0x1c:pri[1] offs=0x1f6e

0x1e:pri[2] offs=0x1f4b

0x20:pri[3] offs=0x1f25

0x22:pri[4] offs=0x1f00

0x24:pri[5] offs=0x1edd

0x26:pri[6] offs=0x1eb9

block_row_dump:

tab 0, row 0, @0x1f82 --//表0 cluster table deptx_cluster

tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1

--//fb: K-H-FL--, K meaning cluster key.

--//The remaining rows have the second high order bit set (fb: -CH-FL--),

--//C meaning cluster table member.

curc: 6 comc: 6 pk: 0x0100022b.0 nk: 0x0100022b.0

--//curc: 6 Current row count for this key in this block

--//comc: 6 Committed row count for this key in this block

--//pk: pk: 0x0100022b.0 ROWID of Previous block for this cluster key

--//nk: 0x0100022b.0 ROWID of Next block for this cluster key

col 0: [ 2] c1 15 --//數字20.

tab 1, row 0, @0x1f6e

tl: 20 fb: -CH-FL-- lb: 0x2 cc: 2 cki: 0 --//表1 deptx

--//C meaning cluster table member.

col 0: [ 8] 52 45 53 45 41 52 43 48 --//對應內容'RESEARCH'

col 1: [ 6] 44 41 4c 4c 41 53 --//對應內容'DALLAS'

tab 2, row 0, @0x1f4b --//表2 empx

tl: 35 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0

col 0: [ 3] c2 4a 46

col 1: [ 5] 53 4d 49 54 48

col 2: [ 5] 43 4c 45 52 4b

col 3: [ 3] c2 50 03

col 4: [ 7] 77 b4 0c 11 01 01 01

col 5: [ 2] c2 09

tab 2, row 1, @0x1f25

tl: 38 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0

col 0: [ 3] c2 4c 43

col 1: [ 5] 4a 4f 4e 45 53

col 2: [ 7] 4d 41 4e 41 47 45 52

col 3: [ 3] c2 4f 28

col 4: [ 7] 77 b5 04 02 01 01 01

col 5: [ 3] c2 1e 4c

tab 2, row 2, @0x1f00

tl: 37 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0

col 0: [ 3] c2 4e 59

col 1: [ 5] 53 43 4f 54 54

col 2: [ 7] 41 4e 41 4c 59 53 54

col 3: [ 3] c2 4c 43

col 4: [ 7] 77 bb 04 13 01 01 01

col 5: [ 2] c2 1f

tab 2, row 3, @0x1edd

tl: 35 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0

col 0: [ 3] c2 4f 4d

col 1: [ 5] 41 44 41 4d 53

col 2: [ 5] 43 4c 45 52 4b

col 3: [ 3] c2 4e 59

col 4: [ 7] 77 bb 05 17 01 01 01

col 5: [ 2] c2 0c

tab 2, row 4, @0x1eb9

tl: 36 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0

col 0: [ 3] c2 50 03

col 1: [ 4] 46 4f 52 44

col 2: [ 7] 41 4e 41 4c 59 53 54

col 3: [ 3] c2 4c 43

col 4: [ 7] 77 b5 0c 03 01 01 01

col 5: [ 2] c2 1f

end_of_block_dump

End dump data blocks tsn: 4 file#: 4 minblk 555 maxblk 555

--//透過bbed觀察:

BBED> p dba 4,555 kdbt

struct kdbt[0], 4 bytes @114

sb2 kdbtoffs @114 0

sb2 kdbtnrow @116 1

struct kdbt[1], 4 bytes @118

sb2 kdbtoffs @118 1

sb2 kdbtnrow @120 1

struct kdbt[2], 4 bytes @122

sb2 kdbtoffs @122 2

sb2 kdbtnrow @124 5

--//與如下對應:

0xe:pti[0] nrow=1 offs=0

0x12:pti[1] nrow=1 offs=1

0x16:pti[2] nrow=5 offs=2

--//比如可以看出對於當前塊表empx位於*kdbr[2]開始.

BBED> x /rn *kdbr[0]

rowdata[201] @8166

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

flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK) --//cluster key.行頭為ac 普通表為2c

lock@8167: 0x00

cols@8168: 1

kref@8169: 6

mref@8171: 6

hrid@8173:0x0100022b.0

nrid@8179:0x0100022b.0

col 0[2] @8185: 20

BBED> x /rcc *kdbr[1]

rowdata[181] @8146

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

flag@8146: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC) --//cluser table member .行頭為6c 刪除為7c,實際上加入KDRHFD標誌.

lock@8147: 0x02

cols@8148: 2

col 0[8] @8150: RESEARCH

col 1[6] @8159: DALLAS

--//注:不包括cluster table key.

BBED> x /rnccntnnn *kdbr[2]

rowdata[146] @8111

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

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

lock@8112: 0x02

cols@8113: 6

col 0[3] @8115: 7369

col 1[5] @8119: SMITH

col 2[5] @8125: CLERK

col 3[3] @8131: 7902

col 4[7] @8135: 1980-12-17 00:00:00

col 5[2] @8143: 800

4.做一個刪除的手工恢復看看.

SCOTT@book> delete from empx where empno=7566;

1 row deleted.

SCOTT@book> commit ;

Commit complete.

SCOTT@book> alter system flush buffer_cache;

System altered.

SCOTT@book> alter system dump datafile 4 block 555 ;

System altered.

--//僅僅貼出改動部分:

block_row_dump:

tab 0, row 0, @0x1f82

tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1

curc: 6 comc: 5 pk: 0x0100022b.0 nk: 0x0100022b.0

col 0: [ 2] c1 15

--//comc 5 少1條記錄.

...

tl: 35 fb: -CH-FL-- lb: 0x0 cc: 6 cki: 0

col 0: [ 3] c2 4a 46

col 1: [ 5] 53 4d 49 54 48

col 2: [ 5] 43 4c 45 52 4b

col 3: [ 3] c2 50 03

col 4: [ 7] 77 b4 0c 11 01 01 01

col 5: [ 2] c2 09

tab 2, row 1, @0x1f25

tl: 4 fb: -CHDFL-- lb: 0x2 cc: 0 cki: 0 --//加入D標誌.

tab 2, row 2, @0x1f00

--//如果手工修復,需要修改2處.

BBED> x /rn *kdbr[0]

rowdata[201] @8166

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

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

lock@8167: 0x00

cols@8168: 1

kref@8169: 6

mref@8171: 5

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

hrid@8173:0x0100022b.0

nrid@8179:0x0100022b.0

col 0[2] @8185: 20

BBED> x /rnccntnnn *kdbr[3]

rowdata[108] @8073

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

flag@8073: 0x7c (KDRHFL, KDRHFF, KDRHFD, KDRHFH, KDRHFC)

lock@8074: 0x02

cols@8075: 0

--//執行如下:

assign /d offset 8171=6;

assign /x offset 8073=6c;

BBED> assign /d offset 8171=6;

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

ub1 rowdata[0] @8171 0x06

BBED> assign /x offset 8073=6c;

ub1 rowdata[0] @8073 0x6c

--//檢查:

BBED> x /rn *kdbr[0]

rowdata[201] @8166

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

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

lock@8167: 0x00

cols@8168: 1

kref@8169: 6

mref@8171: 6

hrid@8173:0x0100022b.0

nrid@8179:0x0100022b.0

col 0[2] @8185: 20

BBED> x /rnccntnnn *kdbr[2]

rowdata[146] @8111

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

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

lock@8112: 0x00

cols@8113: 6

col 0[3] @8115: 7369

col 1[5] @8119: SMITH

col 2[5] @8125: CLERK

col 3[3] @8131: 7902

col 4[7] @8135: 1980-12-17 00:00:00

col 5[2] @8143: 800

BBED> sum apply

Check value for File 4, Block 555:

current = 0xf209, required = 0xf209

BBED> verify

DBVERIFY - Verification starting

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

BLOCK = 555

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

data header at 0x7fd6f1fd9264

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

used=263 fsc=34 avsp=7825 dtl=8088

Block 555 failed with check code 6110

--//空間問題暫時不理會.

--//驗證修改是否有效.

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

ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

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

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

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

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

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

--//empno=7566可以查詢到.如果透過索引是無法查詢到資訊.

SCOTT@book> select rowid,empx.* from empx where empno=7566;

no rows selected

--//如果不修復comc的偏移,還原:

BBED> assign /d offset 8171=5;

ub1 rowdata[0] @8171 0x05

BBED> sum apply

Check value for File 4, Block 555:

current = 0xf109, required = 0xf109

BBED> verify

DBVERIFY - Verification starting

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

BLOCK = 555

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

data header at 0x105cc64

kdbchk: key comref count wrong

keyslot=0

Block 555 failed with check code 6121

--//會報如上錯誤.不過查詢沒有問題.

SCOTT@book> alter system flush buffer_cache;

System altered.

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

ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

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

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

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

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

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

5.最後修復bbed verify的錯誤.

--//這個錯誤我建議不熟悉bbed,不再修復,比較繁瑣.

BBED> assign /d offset 8171=6;

ub1 rowdata[0] @8171 0x06

BBED> sum apply

Check value for File 4, Block 555:

current = 0xf209, required = 0xf209

BBED> verify

DBVERIFY - Verification starting

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

BLOCK = 555

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

data header at 0x105cc64

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

used=263 fsc=34 avsp=7825 dtl=8088

Block 555 failed with check code 6110

--//理論used+fsc+avsp=dtl

--//dtl-used-fsc= 8088-263-34 = 7791

--//然後assign kdbh.kdbhavsp=7791,如果修改事務itl槽資訊,步驟也不少.

BBED> assign kdbh.kdbhavsp=7791

sb2 kdbhavsp @110 7791

BBED> sum apply

Check value for File 4, Block 555:

current = 0xf2f7, required = 0xf2f7

BBED> verify

DBVERIFY - Verification starting

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

BLOCK = 555

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

data header at 0x107ec64

kdbchk: space available on commit is incorrect

tosp=7863 fsc=34 stb=0 avsp=7791

Block 555 failed with check code 6111

--//tosp - fsc - stb = avsp.

--//avsp+fsstb= 7791+34+0 = 7825.

BBED> assign kdbh.kdbhtosp=7825

sb2 kdbhtosp @112 7825

BBED> sum apply

Check value for File 4, Block 555:

current = 0xf2d1, required = 0xf2d1

BBED> verify

DBVERIFY - Verification starting

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

BLOCK = 555

DBVERIFY - Verification complete

Total Blocks Examined : 1

Total Blocks Processed (Data) : 1

Total Blocks Failing (Data) : 0

Total Blocks Processed (Index): 0

Total Blocks Failing (Index): 0

Total Blocks Empty : 0

Total Blocks Marked Corrupt : 0

Total Blocks Influx : 0

Message 531 not found; product=RDBMS; facility=BBED

6.修復索引:

SCOTT@book> alter index pk_empx rebuild online;

Index altered.

--//注意一定要加online,不然不回表,無法修復錯誤索引錯誤.

SCOTT@book> select rowid,empx.* from empx where empno=7566;

ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

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

總結:

--//修復cluster table要比普通表有難度.

[20181226]簡單探究cluster table.txt

相關文章