ITPub博客

首页 > Linux操作系统 > Linux操作系统 > LOB (3)

LOB (3)

原创 Linux操作系统 作者:ylw66 时间:2009-09-25 10:52:14 0 删除 编辑

LOB2),除了12chunk情况外,当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 Locator20个字节的数据,76 8flob 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_typeLOB

 

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

LOBINDEXdatafile 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

很明显是一种indexblock结构,有两列数据,第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 00HELLO

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插入10000a的记录

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,按之前的分析是使用了3chunk

 

再插入一行32767a

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 id76 f3lobindex

2 32767a使用了9chunk

row#3中记录了4chunkRDBA02 c0 00 6c 02 c0 00 6d 02 c0 00 6e 02 c0 00 6f

row#4中记录了5chunkRDBA02 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 LOB00 00 00 04表示偏移了4chunk

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22111412/viewspace-615532/,如需转载,请注明出处,否则将追究法律责任。

上一篇: LOB (2)
请登录后发表评论 登录
全部评论

注册时间:2009-07-29

  • 博文量
    57
  • 访问量
    94455