[20130307]clob字段的简单探究2.txt

0
0
0
1. 云栖社区>
2. 博客>
3. 正文

## [20130307]clob字段的简单探究2.txt

lfreeali 2013-03-07 11:21:04 浏览768

[20130307]clob字段的简单探究2.txt

http://space.itpub.net/267265/viewspace-755269

http://www.juliandyke.com/Presentations/LOBInternals.ppt

1.clob字段NULL与empty_clob()是不同的。
2.如果clob字段有信息，除了保存信息外额外要消耗36字节来保存相关信息。

1.如果保存的长度小于等于3964,lob的信息保存在块内.
2.如果保存的长度大于3964,lob的信息保存在块外的lob segment内.

lob的信息都保存在lob dsegemnt内.
--3964+36正好等于4000.

1.建立测试环境:

`SQL> select * from v\$version where rownum`
`BANNER`
`--------------------------------------------------------------------------------`
`Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production`
`SQL> select * from v\$nls_parameters where parameter='NLS_CHARACTERSET';`
`PARAMETER           VALUE     `
`------------------- ---------`
`NLS_CHARACTERSET    ZHS16GBK`
`--安装的语言选择NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK`
`drop table t purge ;`
`create table t ( id number,c clob);`
`insert into t values (1,lpad('a',12,'a'));`
`insert into t values (2,lpad('b',4000,'b')||lpad('c',4000,'c'));`
`SQL> insert into t values (2,lpad('b',4000,'b')||lpad('c',4000,'c'));`
`insert into t values (2,lpad('b',4000,'b')||lpad('c',4000,'c'))`
`            *`
`ERROR at line 1:`
`ORA-01489: result of string concatenation is too long`
`--不能这样插入,超长了.`
`declare`
`    v1 clob;`
`begin`
`    for idx in 1 .. 2 loop`
`        v1 := v1 || rpad('x', 4000, 'x');`
`    end loop;`
`    insert into t values (2,v1);`
`end;`
`/`
`PL/SQL procedure successfully completed.`
`--奇怪,这样插入与拼接在PL/sql中并不报错.`
`commit ;`
`SQL> column c format a30`
`SQL> select rowid,t.* from t ;`
`SQL> select rowid,t.* from t ;`
`ROWID                      ID C`
`------------------ ---------- ------------------------------`
`AABB2VAAEAAAAWfAAA          1 aaaaaaaaaaaa`
`AABB2VAAEAAAAWfAAB          2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx`
`                              xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx`
`                              xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx`
`                              ...`
`                              xxxxxxxxxxxxxxxxxxxx`
`                          `
`SQL> @lookup_rowid AABB2VAAEAAAAWfAAA`
`    OBJECT       FILE      BLOCK        ROW`
`---------- ---------- ---------- ----------`
`    269717          4       1439          0`
`--我插入一个长度12,另外一个长度8000的字符在clob字段中.`
`--保证写到磁盘.`
`SQL> alter system checkpoint;`
`System altered.`

2.使用bbed观察如下:
`BBED> p *kdbr[1]`
`rowdata[0]`
`----------`
`ub1 rowdata[0]                              @8070     0x2c`
`BBED> x /2rnc`
`rowdata[0]                                  @8070`
`----------`
`flag@8070: 0x2c (KDRHFL, KDRHFF, KDRHFH)`
`lock@8071: 0x01`
`cols@8072:    2`
`col    0[2] @8073: 2`
`col   1[44] @8076: .T..........................................`
`rowdata[51]                                 @8121`
`-----------`
`flag@8121: 0x2c (KDRHFL, KDRHFF, KDRHFH)`
`lock@8122: 0x01`
`cols@8123:    2`
`col    0[2] @8124: 1`
`col   1[60] @8127: .T...................(...............a.a.a.a.a.a.a.a.a.a.a.a`

--第1次插入在块底,clob占用60字节,而第2次插入仅仅占用44字节(不算前面的长度指示器).

3.看数据块的转储:
SQL> alter system dump datafile 4 block 1439;

`Block header dump:  0x0100059f`
` Object id on Block? Y`
` seg/obj: 0x41d95  csc: 0x00.c015c422  itc: 2  flg: E  typ: 1 - DATA`
`     brn: 0  bdba: 0x1000598 ver: 0x01 opc: 0`
`     inc: 0  exflg: 0`
` Itl           Xid                  Uba         Flag  Lck        Scn/Fsc`
`0x01   0x0005.01a.0000351f  0x00c0387e.169d.1b  --U-    2  fsc 0x0000.c015c606`
`0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000`
`bdba: 0x0100059f`
`data_block_dump,data header at 0x2a973b5264`
`===============`
`tsiz: 0x1f98`
`hsiz: 0x16`
`pbl: 0x2a973b5264`
`     76543210`
`flag=--------`
`ntab=1`
`nrow=2`
`frre=-1`
`fsbo=0x16`
`fseo=0x1f22`
`avsp=0x1f0c`
`tosp=0x1f0c`
`0xe:pti[0]  nrow=2  ffs=0`
`0x12:pri[0] ffs=0x1f55`
`0x14:pri[1] ffs=0x1f22`
`block_row_dump:`
`tab 0, row 0, @0x1f55`
`tl: 67 fb: --H-FL-- lb: 0x1  cc: 2`
`col  0: [ 2]  c1 02`
`col  1: [60]            `
` 00 54 00 01 02 0c 80 00 00 02 `
` 00 00 00 01 00 00 01 e1 ca 17 `
` 00 28 09 00 00 00 00 00 00 18`
` 00 00 00 00 00 01 00 61 00 61`
` 00 61 00 61 00 61 00 61 00 61`
` 00 61 00 61 00 61 00 61 00 61`
`LOB`
`Locator:`
`  Length:        84(60)`
`  Version:        1`
`  Byte Length:    2`
`  LobID: 00.00.00.01.00.00.01.e1.ca.17`
`  Flags[ 0x02 0x0c 0x80 0x00 ]:`
`    Type: CLOB`
`    Storage: BasicFile`
`    Enable Storage in Row`
`    Characterset Format: IMPLICIT`
`    Partitioned Table: No`
`    Options: VaringWidthReadWrite`
`  Inode:`
`    Size:     40`
`    Flag:     0x09 [ Valid DataInRow ]`
`    Future:   0x00 (should be '0x00')`
`    Blocks:   0`
`    Bytes:    24`
`    Version:  00000.0000000001`
`    Inline data[24]`
`Dump of memory from 0x0000002A973B71E4 to 0x0000002A973B71FC`
`2A973B71E0          61006100 61006100 61006100      [.a.a.a.a.a.a]`
`2A973B71F0 61006100 61006100 61006100           [.a.a.a.a.a.a]`
`tab 0, row 1, @0x1f22`
`tl: 51 fb: --H-FL-- lb: 0x1  cc: 2`
`col  0: [ 2]  c1 03`
`col  1: [44]            `
` 00 54 00 01 02 0c 80 00 00 02 `
` 00 00 00 01 00 00 01 e1 ca 18`
` 00 18 05 00 00 00 00 01 1e bc `
` 00 00 00 00 00 02 01 00 05 a7 `
` 01 00 05 a3`
`LOB`
`Locator:`
`  Length:        84(44)`
`  Version:        1`
`  Byte Length:    2`
`  LobID: 00.00.00.01.00.00.01.e1.ca.18`
`  Flags[ 0x02 0x0c 0x80 0x00 ]:`
`    Type: CLOB`
`    Storage: BasicFile`
`    Enable Storage in Row`
`    Characterset Format: IMPLICIT`
`    Partitioned Table: No`
`    Options: VaringWidthReadWrite`
`  Inode:`
`    Size:     24`
`    Flag:     0x05 [ Valid InodeInRow(ESIR) ]`
`    Future:   0x00 (should be '0x00')`
`    Blocks:   1`
`    Bytes:    7868`
`    Version:  00000.0000000002`
`    DBA Array[2]:`
`      0x010005a7 0x010005a3`
`end_of_block_dump`
`End dump data blocks tsn: 4 file#: 4 minblk 1439 maxblk 1439`

`col  1: [60]            `
` 00 54 00 01 02 0c 80 00 00 02   `
` 00 00 00 01 00 00 01 e1 ca 17   `
` 00 28 09 00 00 00 00 00 00 18   `
` 00 00 00 00 00 01 `
`                   00 61 00 61   `
` 00 61 00 61 00 61 00 61 00 61`
` 00 61 00 61 00 61 00 61 00 61`
`Inode 占用16bytes`
`Body Length占2bytes: 00 28                  ==> 等于0x28=40,也就是60-前面的20字节(header的长度+lobid的长度)=40(0x28),正好一致.`
`Flags      占2bytes: 09 00                  ==> [ Valid DataInRow ]`
`LOB Length 占6bytes: 00 00 00 00 00 18      ==> 0x18=24,虽然输入12个字符的'a',而实际上保存的信息是unicode变量,字符'a'占用2`
`                                                个字节,这样12个字符'a'占用24个字节.`
`Version    占6bytes: 00 00 00 00 00 01`

5.看看第2条记录的情况:
`col  1: [44]            `
` 00 54 00 01 02 0c 80 00 00 02   `
` 00 00 00 01 00 00 01 e1 ca 18   `
` 00 18 05 00 00 00 00 01 1e bc   `
` 00 00 00 00 00 02`
`                   01 00 05 a7 `
` 01 00 05 a3`
`Inode 占用16bytes`
`Body Length占2bytes: 00 18                 ==> 等于0x18=24,也就是44-前面的20字节(header的长度+lobid的长度)=24(0x18),正好一致.         `
`Flags      占2bytes: 05 00                 ==> [ Valid InodeInRow(ESIR) ] ,说明节点信息在行内,而不是数据本身.`
`LOB Length 占6bytes: 00 00 00 01 1e bc     ==> 01 表示块数量,为什么是1?难道从0开始计数吗?`
`                                               0x1ebc => 7868,这个又表示什么呢?而且我插入信息是8000个'x',应该占用16000字`
`                                               节.`
`Version    占6bytes: 00 00 00 00 00 02`
`blob占的块地址: 01 00 05 a7  01 00 05 a3  ==> 与这个DBA Array[2]: 0x010005a7 0x010005a3相对应.`
`SQL> host cat dfb.sql`
`select`
`dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx')) rfile#,`
`dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) block#`
`from dual;`
`SQL> @dfb 010005a7`
`    RFILE#     BLOCK#`
`---------- ----------`
`         4       1447`
`SQL> @dfb 010005a3`
`    RFILE#     BLOCK#`
`---------- ----------`
`         4       1443`

--占用2个数据块,数据块大小8k.

6.再来转储这两块就能很清楚的知道lob如何存储的了.
`SQL> alter system dump datafile 4 block 1447;`
`System altered.`
`SQL> alter system dump datafile 4 block 1443;`
`System altered.`
`Start dump data blocks tsn: 4 file#:4 minblk 1447 maxblk 1447`
`Block dump from cache:`
`Dump of buffer cache at level 4 for tsn=4, rdba=16778663`
`Block dump from disk:`
`buffer tsn: 4 rdba: 0x010005a7 (4/1447)`
`scn: 0x0000.c015c603 seq: 0x02 flg: 0x04 tail: 0xc6032802`
`frmt: 0x02 chkval: 0xab62 type: 0x28=PAGETABLE MANAGED LOB BLOCK`
`Hex dump of block: st=0, typ_found=1`
`Dump of memory from 0x0000002A96FCCA00 to 0x0000002A96FCEA00`
`2A96FCCA00 0000A228 010005A7 C015C603 04020000  [(...............]`
`2A96FCCA10 0000AB62 00041D96 01000000 E1010000  [b...............]`
`2A96FCCA20 000018CA 00000001 00000000 00000000  [................]`
`2A96FCCA30 010005A0 00000000 78007800 78007800  [.........x.x.x.x]`
`2A96FCCA40 78007800 78007800 78007800 78007800  [.x.x.x.x.x.x.x.x]`
`        Repeat 506 times`
`2A96FCE9F0 78007800 78007800 78007800 C6032802  [.x.x.x.x.x.x.(..]`
`Long field block dump:`
`Object Id   269718`
`LobId: 0001001E1CA18 PageNo        0`
`Version: 0x0000.00000001  pdba: 16778656`
`00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78`
`00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78`
`00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78`
`...`
`    00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78`
`End dump data blocks tsn: 4 file#: 4 minblk 1447 maxblk 1447`
`Start dump data blocks tsn: 4 file#:4 minblk 1443 maxblk 1443`
`Block dump from cache:`
`Dump of buffer cache at level 4 for tsn=4, rdba=16778659`
`Block dump from disk:`
`buffer tsn: 4 rdba: 0x010005a3 (4/1443)`
`scn: 0x0000.c015c603 seq: 0x02 flg: 0x04 tail: 0xc6032802`
`frmt: 0x02 chkval: 0xab67 type: 0x28=PAGETABLE MANAGED LOB BLOCK`
`Hex dump of block: st=0, typ_found=1`
`Dump of memory from 0x0000002A96FCCA00 to 0x0000002A96FCEA00`
`2A96FCCA00 0000A228 010005A3 C015C603 04020000  [(...............]`
`2A96FCCA10 0000AB67 00041D96 01000000 E1010000  [g...............]`
`2A96FCCA20 000018CA 00000001 00000000 00000001  [................]`
`2A96FCCA30 010005A0 00000000 78007800 78007800  [.........x.x.x.x]`
`2A96FCCA40 78007800 78007800 78007800 78007800  [.x.x.x.x.x.x.x.x]`
`        Repeat 490 times`
`2A96FCE8F0 78007800 20002000 20002000 20002000  [.x.x. . . . . . ]`
`2A96FCE900 20002000 20002000 20002000 20002000  [. . . . . . . . ]`
`        Repeat 14 times`
`2A96FCE9F0 20002000 20002000 20002000 C6032802  [. . . . . . .(..]`
`Long field block dump:`
`Object Id   269718`
`LobId: 0001001E1CA18 PageNo        1`
`Version: 0x0000.00000001  pdba: 16778656`
`00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78`
`...`
`00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 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: 4 file#: 4 minblk 1443 maxblk 1443`

`--`
`Start dump data blocks tsn: 4 file#:4 minblk 1447 maxblk 1447`
`2A96FCCA00 0000A228 010005A7 C015C603 04020000  [(...............]`
`2A96FCCA10 0000AB62 00041D96 01000000 E1010000  [b...............]`
`2A96FCCA20 000018CA 00000001 00000000 00000000  [................]`
`2A96FCCA30 010005A0 00000000 78007800 78007800  [.........x.x.x.x]`
`--前面header:14*4=56个字节,里面的信息(我乱猜一些)`
`010005A7:块地址`
`scn: 0x0000.c015c603 seq: 0x02 flg: 0x04 `
`00041D96=>object_id  269718=>0x41D96`
`--后面结尾tailer:4个字节.应该是校验信息`

--这样实际的lob 1块最多占用 8192-56-4=8132字节 .如果不足后面补0020应该是空格的编码.
--16000-8132=7868,正好说明前面的7868是保存最后一个块数据剩余信息的长度.

http://space.itpub.net/267265/viewspace-752202  [20130106]关于不同字符集下clob字段的存储问题.txt
http://space.itpub.net/267265/viewspace-755269  [20130301]clob字段的empty_clob与NULL.txt

lfreeali
+ 关注