ITPub博客

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

LOB (1)

原创 Linux操作系统 作者:ylw66 时间:2009-09-24 15:57:14 0 删除 编辑

其实就是对

Expert Oracle Database Architecture Architecture.9i.and.10g.Programming.Techniques.and.Solutions

中的关于LOB内容的笔记,书上写的关于LOB的非常好

 

SQL> create table t(id int primary key,t_lob clob)

  2  lob(t_lob) store as t_lob_segment;   -----LOBSEGMENT命名为t_lob_segment

表已创建。

 

SQL> column segment_name format a30

SQL> select segment_name,segment_type from dba_segments t

  2  where t.segment_type in('LOBINDEX','LOBSEGMENT')

  3  and t.owner='NBA';

SEGMENT_NAME                   SEGMENT_TYPE

------------------------------ ------------------

SYS_IL0000031811C00002$$       LOBINDEX

T_LOB_SEGMENT                 LOBSEGMENT

新建t表后,多出两个段一个是LOBINDEX,一个是LOBSEGMENT

 

查看T表的ddl,关于lob默认的ddl是:

LOB("T_LOB") STORE AS ( TABLESPACE "NBA" 
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 
2147483645 PCTINCREASE 0) 
ENABLE STORAGE IN ROW 
NOCACHE CHUNK 8192 PCTVERSION 10)

 

插入一条记录

SQL> insert into t values(1,'HELLO');

已创建 1 行。

SQL> commit;

提交完成。

通过rowid获得数据在datafile 11 block 15

SQL> alter system dump datafile 11 block 15;

系统已更改。


 

col  0: [ 2]  c1 02  ----id1

col  1: [46]   ---- t_lob:HELLO

 00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 00 5b 05

 00 1a 09 00 00 00 00 00 00 0a 00 00 00 00 00 01

 48 00 45 00 4c 00 4c 00 4f 00

 

00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 00 5b 05LOB Locator,这一部分20个字节

其中00 00 00 01 00 00 00 00 5b 05最后10个字节是lob id

 

00 1a 09 00 00 00 00 00 00 0a 00 00 00 00 00 01LOB inode,这一部分16个字节

 

48 00 45 00 4c 00 4c 00 4f 00即数据HELLO

1 HELLO并没有保存到LOBSEGMENT上,是保存在表T的段上

2 SQL> select dump('HELLO',16) from dual;

DUMP('HELLO',16)

----------------------------

Typ=96 Len=5: 48,45,4c,4c,4f

CLOBs are always in fixed width 2-byte charset CLOB中通常2个字节保存一个字符

 

再创建一行,有1980a

SQL> insert into t values(2,lpad('a',1980,'a'));

已创建 1 行。

SQL> commit;

提交完成。

通过dump发现,该数据仍是保存在表T的段上

col  0: [ 2]  c1 03

col  1: [3996]

 00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 00 74 9b 0f 88 09 00 00

 00 00 00 0f 78 00 00 00 00 00 01

 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 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。。。。。。。

 

插入数据1983a

SQL> insert into t values(3,lpad('a',1983,'a'));

已创建 1 行。

SQL> commit;

提交完成。

dump id =3

SQL> select dump(3,16) from dual;

DUMP(3,16)

-----------------

Typ=2 Len=2: c1,4

 

col  0: [ 2]  c1 04 ---id=3

col  1: [40]

00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 00 74 9c

00 14 05 00 00 00 00 00 0f 7e 00 00 00 00 00 02 02 c0 00 15 ---

来看看原来数据位置的02 c0 00 15的代表什么

SQL> select dbms_utility.DATA_BLOCK_ADDRESS_FILE(to_number('02c00015','xxxxxxxxxx')) file#,

  2   dbms_utility.data_block_address_block(to_number('02c00015','xxxxxxxxxx')) block#

  3  from dual;

     FILE#     BLOCK#

---------- ----------

        11         21

 

dump datafile 11 block 21

buffer tsn: 12 rdba: 0x02c00015 (11/21)

scn: 0x0000.01525f7b seq: 0x02 flg: 0x04 tail: 0x5f7b2802

frmt: 0x02 chkval: 0xce0e type: 0x28=PAGETABLE MANAGED LOB BLOCK

Long field block dump:

Object Id    31812

LobId: 00010000749C PageNo        0

Version: 0x0000.00000001  pdba: 46137361 

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

数据原来保存到了LOBSEGMENT上了,而原数据块上保留的是LOBSEGMENT的地址

1 为什么1980a还保留在表的段上,1983a就保留到LOBSEGMENT上呢

LOB属性中ENABLE STORAGE IN ROW 起作用了,即如果lob的数据小于4000个字节(好像varchar24000)一样)就保存在表上。

LOB Locator 20个字节,LOB inode 16个字节,而lob中的字符都是以2个字节存储,所以最小能存

(4000-20-16)/2=1982个字符,所以当字符为1983个时,就保存到LOBSEGMENT上了

 

2 为什么61 00 (表示a)后还有20 00,这些多出的20 00 是什么

LOB的最小读取单位是chunkschunks又叫pagesfatblocksfatblocks很有意思,表明chunk必须是block的整数倍

比如:

SQL> create table t3(id int,value clob)

  2  lob(value) store as(chunk 9000);

表已创建。

好像chunk不是block的整数倍,但是当你查ddl时发现

CREATE TABLE "NBA"."T3" ("ID" NUMBER, "VALUE" CLOB) 
TABLESPACE "NBA" PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 
2147483645 PCTINCREASE 0) 
LOGGING

LOB("VALUE") STORE AS ( TABLESPACE "NBA" 
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 
2147483645 PCTINCREASE 0) 
ENABLE STORAGE IN ROW 
NOCACHE CHUNK 16384 PCTVERSION 10)

Oracle 给你自动转为block的整数倍了

言归正传,T表中chunk就是1block那么大,由于chunklob的读取最小的单位,所以当分配了一个chunk后,1983a并没有达到chunk的大小,所以多余的部分补的是20 00.查下ascii码就知道20 00是空格转为16进制


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

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

注册时间:2009-07-29

  • 博文量
    57
  • 访问量
    94471