oracle clob类型列的内部存储结构初步解析。
Clob 类型字段的内部存储初步探索
1。Dsi 中介绍的LOB类型的内部结构。
There are three structures for internal LOBs:
– LOB Locator: kolbl (20 bytes)
– LOB Inode: kdlinode (16 bytes minimum)
– Data array
• The physical location of these three components
could be up to three different segments:
len(2), vsn(2), flg(4), bytl(2), lobid(10), inode(16),
data
len = maximum length of the LOB locator excluding these two len bytes
vsn = version of the LOB locator structure
flg = 4 flag bytes
bytl = byte length (1 for BLOB/CLOB/BFILE. Value for NCLOB)
lobid = 2 bytes for index followed by 8 byte LOB OID. Key into LOB index
inode = kdlinode structure
data = actual LOB data
Internal LOB locator without inode:
len(2), vsn(2), flg(4), bytl(2), lobid(10)
Len = maximum length of the LOB locator excluding these two len bytes
vsn = version of the LOB locator structure
flg = 4 flag bytes
bytl = byte length (1 for BLOB/CLOB/BFILE. Value for NCLOB)
lobid = 2 bytes for index followed by 8 byte LOB OID. Key into LOB index
SQL*Plus: Release 10.2.0.3.0 - Production on ÐÇÆÚ¶þ 5ÔÂ 27 04:46:43 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> drop user lsliang cascade ;
User dropped.
SQL> drop tablespace test ;
Tablespace dropped.
SQL> create tablespace test datafile '/oracle/oradata/sunha5/test01.dbf' size 1m reuse;
Tablespace created.
SQL> create user lsliang identified by lsliang ;
User created.
SQL> grant dba to lsliang ;
Grant succeeded.
SQL> conn lsliang/lsliang ;
Connected.
SQL> create table test (id number ,name varchar2(10), text clob) tablespace test ;
Table created.
SQL> insert into test values( 1,'lsl','aaaaa') ;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL>
SQL> select rowid from test ;
ROWID
------------------
AAAPIoAAVAAAAAQAAA
SQL>
[oracle@sunha2 baby]$ perl rowid.pl AAAPIoAAVAAAAAQAAA
For this Rowid : AAAPIoAAVAAAAAQAAA
The dba_objets -- data_object_id = 61992
The data_file -- datafile# = 21
The data_block -- data_block# = 16
This Record in the datablock is 0
## rowid.pl 是一个解析rowid 的perl脚本，具体rowid 如何解析请参考网络上的文章。
[oracle@sunha5 sunha5]$ ./a.out test01.dbf 16
file#: 21
block#: 16
type: 0X6
fmt:0Xa2 --0X02
rdba: 0X5400010
bas SCN:0Xf4a9a73
wrap SCN: 0X0000
flag: 0X6
chk: 0Xb6e0
数据块的块头的信息。 A.out 为解析数据文件块头的c程序，具体请参考网络上数据块的结构。
Vi a1
*** 2008-05-27 05:44:53.154
*** ACTION NAME:() 2008-05-27 05:44:53.153
*** MODULE NAME:(SQL*Plus) 2008-05-27 05:44:53.153
*** SERVICE NAME:(SYS$USERS) 2008-05-27 05:44:53.153
*** SESSION ID:(1652.49666) 2008-05-27 05:44:53.153
Start dump data blocks tsn: 22 file#: 21 minblk 16 maxblk 16
buffer tsn: 22 rdba: 0x05400010 (21/16)
scn: 0x0000.0f4a9a73 seq: 0x03 flg: 0x06 tail: 0x9a730603
frmt: 0x02 chkval: 0xb6e0 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000106768C00 to 0x000000010676AC00
106768C00 06A20000 05400010 0F4A9A73 00000306 [.....@...J.s....]
106768C10 B6E00000 01270019 0000F228 0F4A99DE [.....'.....(.J..]
106768C20 00000000 1F023200 05400009 00370026 [......2..@...7.&]
106768C30 00000619 00800384 01052D00 20010000 [..........-. ...]
......................
......................
10676AB90 010078D0 2C000FFF 00000000 FFFFFFFF [..x.,...........]
10676ABA0 7FFEEEB8 00000004 1E629308 003E002C [.........b...>.,]
10676ABB0 00008000 00000027 C20F3F4B C5162F24 [.......'..?K../$]
10676ABC0 154A062C 010302C1 02036C73 6C2E0054 [.J.,......lsl..T]
10676ABD0 0001020C 80000002 00000001 00000038 [...............8]
10676ABE0 EF33001A 09000000 0000000A 00000000 [.3..............]
10676ABF0 00010061 00610061 00610061 9A730603 [...a.a.a.a.a.s..]
Block header dump: 0x05400010
Object id on Block? Y
seg/obj: 0xf228 csc: 0x00.f4a99de itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x5400009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0037.026.00000619 0x00800384.0105.2d --U- 1 fsc 0x0000.0f4a9a73
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x106768c64
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x106768c64
bdba: 0x05400010
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f5f
avsp=0x1f4b
tosp=0x1f4b
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f5f
block_row_dump:
tab 0, row 0, @0x1f5f
tl: 57 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [ 3] 6c 73 6c
col 2: [46]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 38 ef 33 00 1a 09 00 00
00 00 00 00 0a 00 00 00 00 00 01 00 61 00 61 00 61 00 61 00 61
end_of_block_dump
End dump data blocks tsn: 22 file#: 21 minblk 16 maxblk 16
解说：
对于数据长度小于４０００的情况，lob列的数据存放在行内，如上图，数据的存储格式是ascii码存放。
Lob数据的结构，头部最大为８４字节，最小为３６字节
对于本例：
2E0054 [.J.,......lsl..T]
10676ABD0 0001020C 80000002 00000001 00000038 [...............8]
10676ABE0 EF33001A 09000000 0000000A 00000000 [.3..............]
10676ABF0 00010061 00610061 00610061 9A730603 [...a.a.a.a.a.s..]
２E : 列的长度４６ －－ col 2: [46]
0054 lob head 的最大长度，
0001 vsn
02 0c 80 00 flag
00 02 数据的单位长度为２个字节。
00 00 00 01 00 00 00 38 ef 33 lob_id
00 1a 09 00 00 00 00 00 00 0a 00 00 00 00 00 01 inodeid
00 61 00 61 00 61 00 61 00 61 data数据： 我们插入的那５个a .
经过我的测试 对inodeid 最后一位是一个开关变量（01,02)
01 表示数据在行内存放，
02 表示数据存储在log段中。
SQL> @a.sql;
PL/SQL procedure successfully completed.
SQL> alter system checkpoint ;
System altered.
SQL> commit;
Commit complete.
More a.sql ;
--------------------------
declare
a clob;
b varchar2(32000) ;
begin
b:=lpad('a',4000,'a') ;
a:=a||b ;
--a:=a||a;
--a:=a||a;
--a:=a||a;
--a:=a||a;
--a:=a||a;
--a:=a||a;
update lsliang.test set text=a ;
commit ;
end ;
/
---------------------------------
## a.sql 用来更新lob字段。
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> alter system dump datafile 21 block 16 ;
System altered.
SQL>
Block header dump: 0x05400010
Object id on Block? Y
seg/obj: 0xf228 csc: 0x00.f4b132c itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x5400009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0037.026.00000619 0x00800384.0105.2d C--- 0 scn 0x0000.0f4a9a73
0x02 0x0017.026.00000ae0 0x00801869.01b9.3c --U- 1 fsc 0x0006.0f4b132d
data_block_dump,data header at 0x106768c64
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x106768c64
bdba: 0x05400010
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f2c
avsp=0x1f4b
tosp=0x1f51
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f2c
block_row_dump:
tab 0, row 0, @0x1f2c
tl: 51 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 02
col 1: [ 3] 6c 73 6c
col 2: [40]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 38 ef 34 00 14 05 00 00
00 00 00 1f 40 00 00 00 00 00 02 05 40 00 18
end_of_block_dump
End dump data blocks tsn: 22 file#: 21 minblk 16 maxblk 16
0054 lob head 的最大长度，
0001 vsn
02 0c 80 00 flag
00 02 数据的单位长度为２个字节。
00 00 00 01 00 00 00 38 ef 34 lob_id
00 14 05 00 00 00 00 00 1f 40 00 00 00 00 00 02 inodeid
05 40 00 18 data部分没有了，取而代之的是rdba
我们来看看 ox05400018放的是什么东西。
SQL*Plus: Release 10.2.0.3.0 - Production on ÐÇÆÚ¶þ 5ÔÂ 27 07:00:18 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> alter system dump datafile 21 block 24 ;
System altered.
SQL>
[oracle@sunha5 sunha5]$ ./a.out test01.dbf 24
file#: 21
block#: 24
type: 0X28
fmt:0Xa2 --0X02
rdba: 0X5400018
bas SCN:0Xf4b132c
wrap SCN: 0X0000
flag: 0X4
chk: 0X10c3
*** 2008-05-27 07:00:32.904
*** SERVICE NAME:(SYS$USERS) 2008-05-27 07:00:32.904
*** SESSION ID:(510.3928) 2008-05-27 07:00:32.904
Start dump data blocks tsn: 22 file#: 21 minblk 24 maxblk 24
buffer tsn: 22 rdba: 0x05400018 (21/24)
scn: 0x0000.0f4b132c seq: 0x02 flg: 0x04 tail: 0x132c2802
frmt: 0x02 chkval: 0x10c3 type: 0x28=PAGETABLE MANAGED LOB BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000106768C00 to 0x000000010676AC00
106768C00 28A20000 05400018 0F4B132C 00000204 [(....@...K.,....]
106768C10 10C30000 0000F229 00000001 00000038 [.......).......8]
106768C20 EF340000 00000001 00000000 00000000 [.4..............]
106768C30 05400011 00000000 00610061 00610061 [.@.......a.a.a.a]
106768C40 00610061 00610061 00610061 00610061 [.a.a.a.a.a.a.a.a]
Repeat 498 times
10676AB70 00610061 00610061 00200020 00200020 [.a.a.a.a. . . . ]
10676AB80 00200020 00200020 00200020 00200020 [. . . . . . . . ]
Repeat 6 times
10676ABF0 00200020 00200020 00200020 132C2802 [. . . . . . .,(.]
Long field block dump:
Object Id 61993
LobId: 000100038EF34 PageNo 0
Version: 0x0000.00000001 pdba: 88080401
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
。。。。。。。。。。。。。。。。。
。。。。。。。。。。。。。。。。
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20
00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20
00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20
00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20
00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20
00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20
End dump data blocks tsn: 22 file#: 21 minblk 24 maxblk 24
可以看出来了，0x05400018 是一个lob段，保存了我们刚才update的数据
（@a.sql update lsliang.test set text=a; a=40000 * a )
从这里可以看出 ：
106768C30 05400011 00000000 00610061 00610061 [.@.......a.a.a.a]
106768C40 00610061 00610061 00610061 00610061 [.a.a.a.a.a.a.a.a]
Repeat 498 times
10676AB70 00610061 00610061 00200020 00200020 [.a.a.a.a. . . . ]
刚好是４９８＊８个a + 8个a + 2*４个a ＝４０００个a 。
再回过头来看看ox05400010
col 2: [40]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 38 ef 34 00 14 05 00 00
00 00 00 1f 40 00 00 00 00 00 02 05 40 00 18
0054 lob head 的最大长度，
0001 vsn
02 0c 80 00 flag
00 02 数据的单位长度为２个字节。
00 00 00 01 00 00 00 38 ef 34 lob_id
00 14 05 00 00 00 00 00 1f 40 00 00 00 00 00 02 inodeid
05 40 00 18 data部分没有了，取而代之的是rdba
看到了吧 inodeid的最后一位是02 即数据存储行外的log段，其后面放置的就是
Log列数据存储的log段的rdba.
还有一个 00 14 05 00 00 00 00 00 1f 40 00 00 00 00 00 02 的红色部分“00 00 00 00 00 1f 40”表示的就是我们存储的数据的长度的１６进制的表示。
[oracle@test231 ~]$ hex2i 0x1f40
argv[] = 8000 -- 8 -- 17500 -- 16 0x1f40
[oracle@test231 ~]$ ## hex2i 为１０，８ ，１６进制之间转换的小脚本请自行实现。
我们实际存储的是４０００个a ，lobhead里定义的bytl＝0x02 两个字节。总共8000字节。
/*注： 对于这个长度的表示，经过进一步研究发现，这个字节仅表示该log列中最后一个block中已经使用的（或已经分配的）字符数
并不代表整个lob列所代表的全部的数据 ，如果log列的数据超过了12个数据块的大小，那么从13个block开始他的rdba就记录在lobindex里面了
所以，如果我们要解析lob数据，还需要增加一个解析 lobindex的过程来读出所有的block_id ,
对于lob列数据小于12个数据块的大小，其实是没有索引的， 至少目前看到的这样的。*/
00 14 05 00 00 00 00 00 1f 40 00 00 00 00 00 02 inodeid 中的”00 14 05“这部分是作什么的呢？ 我本人目前还没有解决，欢迎大家发表意见。
再来看 0x05400018 这个lob段，
106768C00 28A20000 05400018 0F4B132C 00000204 [(....@...K.,....]
106768C10 10C30000 0000F229 00000001 00000038 [.......).......8]
106768C20 EF340000 00000001 00000000 00000000 [.4..............]
106768C30 05400011 00000000 00610061 00610061 [.@.......a.a.a.a]
106768C40 00610061 00610061 00610061 00610061 [.a.a.a.a.a.a.a.a]
”28A20000 05400018 0F4B132C 00000204
10C30000“ 这是block head 部分
下面的部分中
0000F229 00000001 00000038 [.......).......8]
106768C20 EF340000 00000001 00000000 00000000 [.4..............]
106768C30 05400011 00000000
" 0000F229" 这部分代表的是objectid oxf229=61993;
QL> select * from dba_objects where object_id=61993;
OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
LSLIANG SYS_LOB0000061992C00003$$ 61993 61993 LOB
"00000001 00000038EF34" 红色部分是lobid
"0000 00000001 " 蓝色部分Version: 0x0000.00000001
"00000000 00000000" 绿色部分是page no 当一个lob列分为多个chunc的时候，page no 确定该block的数据在整个lob列中的位置。
" 05400011 " 黄色部分 为PDＢＡ 即lob段的block的空闲列表(freelist bitmap )
我们对这部分的展开将在以后给出。
"00000000" 最后的这几个０代表什么呢？？
现在时间 ：
[oracle@sunha5 udump]$ date
Tue May 27 08:05:33 CST 2008
又一个不眠之夜。 后续的部分我会继续推出。
[[i] 本帖最后由 babyyellow 于 2008-7-29 14:09 编辑 [/i]]
|
