本文深入探讨了Oracle数据库中LOB数据类型的存储机制，特别是当使用disablestorageinrow属性时，如何通过LOBINDEX定位LOB数据的实际存储位置。文章通过创建特定的表结构，并逐步插入不同大小的数据，展示了LOB数据如何分布在不同的数据块中。
接LOB（2），除了12个chunk情况外，当lob属性为disable storage in row 时，也会使用到LOBINDEX
[code]SQL> create table t2(id int primary key
2 ,t2_lob clob)
3 lob(t2_lob) store as (disable storage in row);
表已创建。
[/code]
SQL> insert into t2 values(1,'HELLO');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select rowid from t2;
ROWID
------------------
AAAIS5AALAAAABOAAA ----
数据在datafile 11 block 78上
OBJECT_ID DATA_FILE_ID BLOCK_NUMBER ROW_NUMBER
---------- ------------ ------------ ----------
33977 11 78 0
SQL> alter system dump datafile 11 block 78;
系统已更改。
col 0: [ 2] c1 02
col 1: [20] 00 54 00 01 02 08 80 00 00 02 00 00 00 01 00 00 00 00 76 8f
数据中只有LOB Locator的20个字节的数据，76 8f是lob id
数据哪里去了？
首先要找到LOBINDEX在的块
SQL> select index_name,index_type from dba_indexes t where t.owner='NBA' and t.table_name='
T2';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
SYS_C003448 NORMAL
SYS_IL0000033977C00002$$ LOB
在dba_index表中有LOBINDEX的相关记录，index_type为LOB
SQL> select t.segment_name,t.header_file,t.header_block
2 from dba_segments t
3 where t.segment_type='LOBINDEX' and t.owner='NBA' and t.segment_name='SYS_I
L0000033977C00002$$';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
SYS_IL0000033977C00002$$ 11 91
LOBINDEX从datafile 11 block 91 开始
我们dump block 92
Leaf block dump
===============
header address 82317412=0x4e81064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 7936=0x1f00
kdxcoavs 7896
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 32
kdxlebksz 8036
row#0[7986] flag: -----, lock: 0, data:(32):
00 20 03 00 00 00 00 00 00 0a 00 00 00 00 00 01
02 c0 00 56 00 00 00 00 00 00 00 00 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 00 76 8f
col 1; len 4; (4): 00 00 00 00
很明显是一种index的block结构，有两列数据，第1列是lob id=76 8f，第2列是什么再试验下就明白了
02 c0 00 56 正是RDBA，说明数据在datafile 11 block 86上
SQL> select dbms_utility.DATA_BLOCK_ADDRESS_FILE(to_number('02c00056','xxxxxxxxx
x')) file#,
2 dbms_utility.data_block_address_block(to_number('02c00056','xxxxxxxxxx')) b
lock#
3 from dual;
FILE# BLOCK#
---------- ----------
11 86
再dump datafile 11 block 86
48 00 45 00 4c 00 4c 00 4f 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
48 00 45 00 4c 00 4c 00 4f 00是HELLO
20 00 是空格
通过LOBINDEX找到数据了
再插入一行
SQL> insert into t2 values(2,lpad('a',100,'a'));
已创建 1 行。
SQL> commit;
提交完成。
data block：
block_row_dump:
tab 0, row 0, @0x1f7d
tl: 27 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [20] 00 54 00 01 02 08 80 00 00 02 00 00 00 01 00 00 00 00 76 8f
tab 0, row 1, @0x1f62
tl: 27 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [20] 00 54 00 01 02 08 80 00 00 02 00 00 00 01 00 00 00 00 76 90
LOBINDEX block：
row#0[7986] flag: -----, lock: 0, data:(32):
00 20 03 00 00 00 00 00 00 0a 00 00 00 00 00 01
02 c0 00 56 00 00 00 00 00 00 00 00 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 00 76 8f
col 1; len 4; (4): 00 00 00 00
row#1[7936] flag: -----, lock: 2, data:(32):
00 20 03 00 00 00 00 00 00 c8 00 00 00 00 00 01
02 c0 00 57 00 00 00 00 00 00 00 00 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 00 76 90
col 1; len 4; (4): 00 00 00 00
按之前介绍的方法，使用dbms_lob.write插入10000个a的记录
LOBINDEX block：
row#0[7986] flag: -----, lock: 0, data:(32):
00 20 03 00 00 00 00 00 00 0a 00 00 00 00 00 01 02 c0 00 56 00 00 00 00 00
00 00 00 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 00 76 8f
col 1; len 4; (4): 00 00 00 00
row#1[7936] flag: -----, lock: 0, data:(32):
00 20 03 00 00 00 00 00 00 c8 00 00 00 00 00 01 02 c0 00 57 00 00 00 00 00
00 00 00 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 00 76 90
col 1; len 4; (4): 00 00 00 00
row#2[7886] flag: -----, lock: 2, data:(32):
00 20 03 00 00 00 00 02 0e 98 00 00 00 00 00 01
02 c0 00 54 02 c0 00 55 02 c0 00 58 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 00 76 c1
col 1; len 4; (4): 00 00 00 00
依然第二列还是col 1; len 4; (4): 00 00 00 00,但很显然id=3的这行的lob已经不止使用一个chunk，按之前的分析是使用了3个chunk
再插入一行32767个a
row#0[7986] flag: -----, lock: 0, data:(32):
00 20 03 00 00 00 00 00 00 0a 00 00 00 00 00 01 02 c0 00 56 00 00 00 00 00
00 00 00 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 00 76 8f
col 1; len 4; (4): 00 00 00 00
row#1[7936] flag: -----, lock: 0, data:(32):
00 20 03 00 00 00 00 00 00 c8 00 00 00 00 00 01 02 c0 00 57 00 00 00 00 00
00 00 00 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 00 76 90
col 1; len 4; (4): 00 00 00 00
row#2[7886] flag: -----, lock: 0, data:(32):
00 20 03 00 00 00 00 02 0e 98 00 00 00 00 00 01 02 c0 00 54 02 c0 00 55 02
c0 00 58 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 00 76 c1
col 1; len 4; (4): 00 00 00 00
row#3[7836] flag: -----, lock: 2, data:(32):
00 20 03 00 00 00 00 08 01 de 00 00 00 00 00 01
02 c0 00 6c 02 c0 00 6d 02 c0 00 6e 02 c0 00 6f
col 0; len 10; (10): 00 00 00 01 00 00 00 00 76 f3
col 1; len 4; (4): 00 00 00 00
row#4[7786] flag: -----, lock: 2, data:(32):
02 c0 00 70 02 c0 00 6a 02 c0 00 6b 02 c0 00 74
02 c0 00 75 00 00 00 00 00 00 00 00 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 00 76 f3
col 1; len 4; (4): 00 00 00 04
1 这次LOBINDEX多了2条记录，都用来保存lob id为76 f3的lobindex
2 32767个a使用了9个chunk，
row#3中记录了4个chunk的RDBA：02 c0 00 6c 02 c0 00 6d 02 c0 00 6e 02 c0 00 6f
row#4中记录了5个chunk的RDBA：02 c0 00 70 02 c0 00 6a 02 c0 00 6b 02 c0 00 74
02 c0 00 75
3 row#4中记录了offset for above chunks in LOB，00 00 00 04表示偏移了4个chunk
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22111412/viewspace-615532/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/22111412/viewspace-615532/
扫一扫
2717
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
