Clob 型別欄位的內部儲存初步探索

Lsliang pconline.com.cn

前言：本篇文章僅供參考，此為一家之言，僅代表個人觀點，歡迎有有此愛好的同行門拍磚，其中部分結構還沒有搞清除。

我google / baidu 了n多次，目前尚未在web上發現關於此類的文章。

本人有幸從WEB上得到了一些關於oracle 資料型別內部儲存的介紹類的文章，於是嘗試解析lob型別欄位。廢話來來。開始幹活。：）

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

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

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;

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

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 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位元組。

[oracle@test231 ~]$ ## hex2i　為１０，８　，１６進位制之間轉換的小指令碼請自行實現。

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　

又一個不眠之夜。　後續的部分我會繼續推出。　

翻出了 oracle 一些老東西放這裡了 oracle 資料結構解析

相關文章

翻出了 oracle 一些老東西 放這裡了 oracle 資料結構解析

相關文章

翻出了 oracle 一些老東西放這裡了 oracle 資料結構解析