[20180129]简单探究cluster table(补充)4.txt

最新推荐文章于 2021-12-01 09:45:01 发布

转载最新推荐文章于 2021-12-01 09:45:01 发布 · 474 阅读

本文深入探讨Oracle数据库中集群表的内部结构与操作原理，包括创建、查询、删除记录的过程及对数据块的影响，特别关注mref、hrid、nrid字段的变化与BBED工具的显示问题。

[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参数.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2565166/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2565166/