Oracle基本数据类型存储格式浅析

yanger8689

感谢

husthxd

最初由 yangtingkun 发布
[B]前一阵写了Oracle基本数据类型存储格式浅析，对各种数量类型的存储进行了简单的描述，而后又写了一篇repare包修复坏块，其中自己写了一个程序包来恢复DUMP后的数据。但是那个程序包主要是针对repare包生成的结果的，因此通用性不好。

这篇文章将那个程序包修改并简化，变为一个函数。下面给出这个函数的实现和使用例子：
[/B]

如果加点注释上去就最好不过了

yangtingkun

最初由 husthxd 发布
[B]


如果加点注释上去就最好不过了 [/B]

习惯不好，不爱写注释

五一有时间加上。

winkexp

有一个疑问~

以上所讨论的各种字段类型的存储格式，是否可以认为是oracle数据的逻辑存储格式呢？

因为在实际的数据文件并非如此存储的~

看看我的测试：

[php]
1、原始表里的记录
SQL> desc test.test
名称                 空?      类型
----------------------------------------- -------- ---------

A                                                  NUMBER
B                                                  CHAR(5)


SQL> select * from test.test;

         A B
---------- -----
         1 A
         2 b
         3 c
         0 A
           B

2、段上的相关信息
SQL> select relative_fno,block_id,blocks from dba_extents where segment_name='TEST';

RELATIVE_FNO   BLOCK_ID     BLOCKS
------------ ---------- ----------
           7        112          5

3、实际的存储位置
SQL> select rowid,a,b from test.test;

ROWID                       A B
------------------ ---------- -----
AAABCMAAHAAAABxAAA          1 A
AAABCMAAHAAAABxAAB          2 b
AAABCMAAHAAAABxAAC          3 c
AAABCMAAHAAAABxAAD          0 A
AAABCMAAHAAAABxAAE            B

--根据rowid 可以知道数据行存在file# 7 block# 113上

4、dump块内容
alter system dump datafile 'c:\oracle\oradata\dul.dbf' block 113;
--注：该数据文件是手工checkpoint后copy出来的离线文件

trace file:
data_block_dump
===============
tsiz: 0x1fa0
hsiz: 0x1c
pbl: 0x02156c5c
bdba: 0x01c00071
flag=-----------
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f67
avsp=0x1f4b
tosp=0x1f4b
0xeti[0]        nrow=5        offs=0
0x12ri[0]        offs=0x1f94
0x14ri[1]        offs=0x1f88
0x16ri[2]        offs=0x1f7c
0x18ri[3]        offs=0x1f71
0x1ari[4]        offs=0x1f67
block_row_dump:
tab 0, row 0, @0x1f94
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col  0: [ 2]  c1 02
col  1: [ 5]  41 20 20 20 20
tab 0, row 1, @0x1f88
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col  0: [ 2]  c1 03
col  1: [ 5]  62 20 20 20 20
tab 0, row 2, @0x1f7c
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col  0: [ 2]  c1 04
col  1: [ 5]  63 20 20 20 20
tab 0, row 3, @0x1f71
tl: 11 fb: --H-FL-- lb: 0x0 cc: 2
col  0: [ 1]  80
col  1: [ 5]  41 20 20 20 20
tab 0, row 4, @0x1f67
tl: 10 fb: --H-FL-- lb: 0x0 cc: 2
col  0: *NULL*
col  1: [ 5]  42 20 20 20 20
end_of_block_dump
End dump data block from file C:\ORACLE\ORADATA\DUL.DBF minblk 113 maxblk 113

5、寻找该行物理存储字节
以第一行为例

tab 0, row 0, @0x1f94
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col  0: [ 2]  c1 02                    -- 数字1
col  1: [ 5]  41 20 20 20 20     -- 字母A

5.1   用UE打开10m大的数据文件，进入16进制编辑模式，查找 41202020  没有搜索结果，可以说明在数据文件中数据的存储格式并非如此

5.2  根据行偏移有  0x1f94 - 0x1f88 =12 bytes
使用BBED查看数据文件上该地址的值：
BBED> info
File#  Name                                                        Size(blks)
-----  ----                                                        ----------
     7  c:\oracle\oradata\dul.dbf                                         1281

BBED> set dba
        DBA             0x01c00071 (29360241 7,113)

BBED> dump /v offset 0x1f94 count 12
File: c:\oracle\oradata\dul.dbf (7)
Block: 113     Offsets: 8084 to 8095  Dba:0x01c00071
-------------------------------------------------------
2d30372d 32363a30 393a3334          l -07-26:09:34

<16 bytes per line>

BBED> dump  offset 0x1f94 count 12
File: c:\oracle\oradata\dul.dbf (7)
Block: 113              Offsets: 8084 to 8095           Dba:0x01c00071
------------------------------------------------------------------------
2d30372d 32363a30 393a3334

<32 bytes per line>

6、dbca写过mydul，不知道dbca是怎么实现对文件的读取的~

[/php]

不知道这样做对不对哈~

yangtingkun

10M大的文件，你测试还不找个小点的
我用ULTRAEDIT是可以找到的，甚至不需要用查找就能直接定位
有兴趣的话，你可以看看这两篇，里面有例子：
http://blog.itpub.net/post/468/9121
http://blog.itpub.net/post/468/13241

winkexp

我就是用的ultraedit阿~
我以前用ultraedit打开过2g的文件~ 嘎嘎

我知道为什么我没找到了~
忘记了了oracle block里存数据是从下往上存的~

还有块尾有4字节的block wrap信息，用于块头尾一致性检验的~

以我这个数据文件为例：
块大小  8192=0x2000h
file# 7   block# 113
113 =0x71h

做个16进制乘法  0x71h X 0x2000h=0xe2000h

这个block的实际地址是从  0xe2000h  到0xe3fffh

这个从ue里头search 字段数据也是可以找到的~
[php]
BBED> dump offset 0x1ff0
File: c:\oracle\oradata\dul.dbf (7)
Block: 113              Offsets: 8176 to 8191           Dba:0x01c00071
------------------------------------------------------------------------
2c020202 c1030562 62202020 01061bd8

这里是block 113最后的16字节内容~  除去最后4字节校验用的~
c1030562 62202020
这8个字节与dump文件中：

tab 0, row 0, @0x1f94
tl: 12 fb: --H-FL-- lb: 0x2 cc: 2
col  0: [ 2]  c1 03
col  1: [ 5]  62 62 20 20 20

内容吻合，哦耶~~ 再看dump文件中的偏移量与bbed的偏移量之差
可以看到，该数据行在bbed dump的偏移量为
0x1ff4=8180

oracle dump中的偏移量为：
tab 0, row 0, @0x1f94  =  8084

注意到前面：
data_block_dump
===============
tsiz: 0x1fa0
hsiz: 0x1c

tsiz  0x1fa0   =8096
8192 - 8096 = 96

可以看到bbed dump的offset要比oracle dump里的offset大96字节

猜测：
bbed 的offset是块偏移量，是相对块内所有8k地址的偏移量；
oracle dump 的offset是行偏移量，是行数据存储空间的偏移量。

[/php]

有时间的话多做一些测试，还有oracle dump文件一些字段含义还要弄懂~
还要再花点时间把块结构弄清楚~

wangzhi_1

要把数据库当一遍才能写到数据文件中去,

winkexp

LZ, 有没有整理好的doc或者pdf或者chm格式的文档阿？
感谢~

tramplhg

厉害！非常感谢

hotiice

看了你的《Oracle基本数据类型存储格式浅析（二）——数字类型》，似乎数值型比字符型节省空间，但我的测试结果好像不是
我的问题贴
http://www.itpub.net/484948.html
请问根据你的经验
使用位操作能在生产中使用么，记录数10亿的表

最初由 yangtingkun 发布
[B]这篇文章主要描述NUMBER类型的数据和如何在数据库中存储的。


Oracle的NUMBER类型最多由三个部分构成，这三个部分分别是最高位表示位、数据部分、符号位。其中负数包含符号位，正数不会包括符号位。另外，数值0比较特殊，它只包含一个数值最高位表示位80，没有数据部分。


正数的最高位表示位大于80，负数的最高位表示位小于80。其中一个正数的最高位是个位的话，则最高位表示位为C1，百位、万位依次为C2、C3，百分位、万分为依次为C0、BF。一个负数的最高位为个位的话，最高位表示位为3E，百位、万位依次为3D、3C，百分位、万分位依次为3F、40。


数据部分每一位都表示2位数。这个两位数可能是从0到99，如果是数据本身是正数，则分别用二进制的1到64表示，如果数据本身是负数，则使用二进制65到2表示。


符号位用66表示。


上面的这些是我通过DUMP结果总结出来的，对于上面提到的这些关系常数，Oracle之所以这样选择是有道理的，我们后面根据例子也可以推导出来，而且会进一步说明为什么会采用这种方式表示。这里列出的意思是使大家先对NUMBER类型数据有一个大概的了解。


下面我们通过一个例子详细说明：








SQL> CREATE TABLE TEST_NUMBER (NUMBER_COL NUMBER);


表已创建。


SQL> INSERT INTO TEST_NUMBER VALUES (0);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (1);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (2);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (25);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (123);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (4100);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (132004078);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (2.01);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (0.3);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (0.00000125);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (115.200003);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (-1);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (-5);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (-20032);


已创建 1 行。


SQL> INSERT INTO TEST_NUMBER VALUES (-234.432);


已创建 1 行。


SQL> COMMIT;


提交完成。


SQL> COL D_NUMBER FORMAT A50
SQL> SELECT NUMBER_COL, DUMP(NUMBER_COL, 16) D_NUMBER FROM TEST_NUMBER;


NUMBER_COL D_NUMBER
---------- --------------------------------------------------
         0 Typ=2 Len=1: 80
         1 Typ=2 Len=2: c1,2
         2 Typ=2 Len=2: c1,3
        25 Typ=2 Len=2: c1,1a
       123 Typ=2 Len=3: c2,2,18
      4100 Typ=2 Len=2: c2,2a
132004078 Typ=2 Len=6: c5,2,21,1,29,4f
      2.01 Typ=2 Len=3: c1,3,2
        .3 Typ=2 Len=2: c0,1f
.00000125 Typ=2 Len=3: be,2,1a
115.200003 Typ=2 Len=6: c2,2,10,15,1,4
        -1 Typ=2 Len=3: 3e,64,66
        -5 Typ=2 Len=3: 3e,60,66
    -20032 Typ=2 Len=5: 3c,63,65,45,66
  -234.432 Typ=2 Len=6: 3d,63,43,3a,51,66


已选择15行。





    下面根据例子得到的结果，对每行进行说明。首先说明两点基本的。DUMP函数返回的TYPE＝2表示DUMP的数据类型是NUMBER，LENGTH＝N表示数值在数据库中存储的长度是N。


1.DUMP(0)的结果是0x80，在前面已经提到，0只有高位表示位，没有数据位。由于0的特殊，既不属于正数，也不属于负数，因此使用高位表示位用80表示就足够了，不会和其它数据冲突，Oracle出于节省空间的考虑将后面数据部分省掉了。但是为什么Oracle选择0x80表示0呢？我们知道正数和负数互为相反数，每个正数都有一个对应的负数。因此如果我们要使用编码表示数值，则表示正数和负数的编码应该各占一半，这样才能保证使Oracle表示数据范围是合理的。而0x80的二进制编码是1000 0000，正好是一个字节编码最大值的一半，因此，Oracle选择0x80来表示0，是十分有道理的。


2.DUMP(1)的结果是0xc102，0xc1表示了最高位个位，0x2表示数值是1。首先，Oracle为什么用C1表示个位呢？其实，道理和刚才的差不多。采用科学计数法，任何一个实数S都可以描述为A.B×10n，A表示整数部分，B表示小数部分，而N表示10的指数部分。当S大于1时，N大于等于0，S小于1时，N小于0。也就是说，采用指数的方式表示，N大于0和N小于0的情况各占一半左右时，Oracle所表示的范围最广。因此，Oracle选择了C1表示个位是最高位的情况。


SQL> SELECT TO_CHAR(ROUND(TO_NUMBER('81', 'XXX') + (TO_NUMBER('FF', 'XXX') - TO_NUMBER('81', 'XXX') + 1)/2), 'XX') FROM DUAL;




TO_
---
C1





为什么ORACLE使用0x2表示1，而不直接使用0x1表示1呢？Oracle每个字节表示2位数，因此对于这个2位数，出现的可能是0～99共100种可能，问题出在0这里。Oracle底层是用C语言实现的，我们知道二进制0在C语言中用作字符串终结符，Oracle为了避免这个问题，因此使用了0x1表示0，并依次类推，使用0x64表示99。


3.DUMP(2)的结果是0xc103。


4.DUMP(25)的结果是0xc11a。前面提到，数据部分是以2位为最小单位保存的。因此对于25来说，最高位表示位仍然是个位，个位上的值是25，根据上面推出的规则，25在存储为0xc11a。


SQL> SELECT TO_CHAR(25 + 1, 'xx') FROM DUAL;




TO_
---
1a





5.DUMP(123)的结果是0xc20218。由于123最高为是百位，所以最高位表示位为0xc2，百位上是1，用0x02表示，个位上是23，用0x18表示。


6.DUMP(4100)的结果是0xc22a。


注意一点，如果数字最后数位上如果是0，Oracle出于节省空间的考虑不会存储。比如：4100只保存百位上的41，12000000只保存百位位上的12，512000只保存万位上的51和百位上的20。


7.DUMP(132004078)的结果是0xc5022101294f。最高位是亿位，因此用0xC5表示，亿位上是1用0x02表示，百位位上是32用0x21表示，万位上是0用0x01表示，百位上是40用0x29表示，个位上78用0x4F表示。


注意：中间数位上的0不能省略。


8.DUMP(2.01)的结果是0xc10302。最高位是个位用0xC1表示，个位上是2用0x03表示，百分位上是1用0x02表示。


注意：个位下面一位是百分位不是十分位。


9.DUMP(0.3)的结果是0xc01f。最高位是百分位，使用0xC0表示，百分位上是30用0x1F表示。


10.DUMP(0.00000125)的结果是0xbe021a。最高位是百万分位，用0xBE表示，最高位上的1用0x02表示，25用0x1a表示。


11.DUMP(115.200003)的结果是0xc20210150104。


12.DUMP(-1)的结果是0x3e6466。最高位个位，用0x3E表示，64表示个位上是1，66是符号位，表示这个数是负数。


负数和正数互为相反数，负数的最高位表示位和它对应的相反数的最高位相加的值是FF。1的最高位表示位是C1，-1的最高位表示位是3E。负数中1用64表示。负数中的数值和它相反数的数据相加是0x66，也就是符号位。正数1用0x02表示，负数1用0x64表示，二者相加是0x66。负数多个一个标识位，用0x66表示。由于正数的表示范围是0x01到0x64，负数的表示范围是0x65到0x02。因此，不会在表示数字时出现的0x66表示。


13.DUMP(-5)的结果是0x3e6066。0x3e表示最高位是个位，0x60表示个位上是5，0x66是符号标识位。0x3E加0xC1是0xFF。0x60加0x06的结果是0x66。


14.DUMP(-20032)的结果是0x3c63654566。最高位是万位，正数的万位是0xC3，因此负数的万位是0x3C。万位上是2，正数用0x03表示，负数为0x63，百位上是0，正数用0x01表示，负数使用0x65表示，个位上是32，正数用0x21表示，负数使用0x45表示。0x66是负数表示位。


15.DUMP(-234.432)的结果是0x3d63433a5166。





根据Oracle的存储特性，还可以推出Oracle的number类型的取值范围。


Oracle的concept上是这样描述的：


The following numbers can be stored in a NUMBER column:


Positive numbers in the range 1 x 10^-130 to 9.99...9 x 10^125 with up to 38 significant digits.


Negative numbers from -1 x 10^-130 to 9.99...99 x 10^125 with up to 38 significant digits.


Zero.


下面来推导出取值范围。


来看符号位，0xC1表示个位。


SQL> select to_number('ff', 'xxx') - to_number('c1', 'xxx') from dual;


TO_NUMBER('FF','XXX')-TO_NUMBER('C1','XXX')
-------------------------------------------
                                         62


由于Oracle是两位、两位存储的，因此最高位相当于62×2=124，而且最高位上最大值是99，因此正数的最大值为9.999……×10^125。


SQL> select to_number('c1', 'xxx') - to_number('80', 'xxx') from dual;


TO_NUMBER('C1','XXX')-TO_NUMBER('80','XXX')
-------------------------------------------
                                         65


最高位相当于65×2=130，因此正数的最小值为1×10^-130。


负数和正数在各使用了一半的编码，因此具有相同的极值范围。

出自：http://blog.itpub.net/post/468/9445 [/B]