[20130301]clob字段的empty_clob与NULL.txt

lfreeali 2013-03-04

SQL

[20130301]clob字段的empty_clob与NULL.txt

工作需要,开始研究一下clob字段.看看函数empty_clob()与null的区别.

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

create table t ( id number,c clob);
insert into t values (1,empty_clob());
insert into t values (2,NULL);
commit ;

SQL> column c format a30
SQL> select rowid,t.* from t ;

ROWID                      ID C
------------------ ---------- ------------------------------
AABB0EAAEAAAAWeAAA          1
AABB0EAAEAAAAWeAAB          2

SQL> @lookup_rowid AABB0EAAEAAAAWeAAA

    OBJECT       FILE      BLOCK        ROW
---------- ---------- ---------- ----------
    269572          4       1438          0

--保证写到磁盘.
SQL> alter system checkpoint;
System altered.

2.使用bbed观察:
BBED> set dba   4,1438
        DBA             0x0100059e (16778654 4,1438)

BBED> map /v
 File: /u01/app/oracle11g/oradata/test/users01.dbf (4)
 Block: 1438                                  Dba:0x0100059e
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0
    ub1 type_kcbh                           @0
    ub1 frmt_kcbh                           @1
    ub1 spare1_kcbh                         @2
    ub1 spare2_kcbh                         @3
    ub4 rdba_kcbh                           @4
    ub4 bas_kcbh                            @8
    ub2 wrp_kcbh                            @12
    ub1 seq_kcbh                            @14
    ub1 flg_kcbh                            @15
    ub2 chkval_kcbh                         @16
    ub2 spare3_kcbh                         @18

 struct ktbbh, 72 bytes                     @20
    ub1 ktbbhtyp                            @20
    union ktbbhsid, 4 bytes                 @24
    struct ktbbhcsc, 8 bytes                @28
    sb2 ktbbhict                            @36
    ub1 ktbbhflg                            @38
    ub1 ktbbhfsl                            @39
    ub4 ktbbhfnx                            @40
    struct ktbbhitl[2], 48 bytes            @44

 struct kdbh, 14 bytes                      @100
    ub1 kdbhflag                            @100
    sb1 kdbhntab                            @101
    sb2 kdbhnrow                            @102
    sb2 kdbhfrre                            @104
    sb2 kdbhfsbo                            @106
    sb2 kdbhfseo                            @108
    sb2 kdbhavsp                            @110
    sb2 kdbhtosp                            @112

 struct kdbt[1], 4 bytes                    @114
    sb2 kdbtoffs                            @114
    sb2 kdbtnrow                            @116

 sb2 kdbr[2]                                @118

 ub1 freespace[8017]                        @122

 ub1 rowdata[49]                            @8139

 ub4 tailchk                                @8188

BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8139     0x2c

BBED> x /2rnc
rowdata[0]                                  @8139
----------
flag@8139: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8140: 0x01
cols@8141:    1

col    0[2] @8142: 2

rowdata[6]                                  @8145
----------
flag@8145: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8146: 0x01
cols@8147:    2

col    0[2] @8148: 1
col   1[36] @8151: .T.................s................

--可以发现两者存在不同,empty_clob()并占用36字节[如果加上前面的长度指示器,占用37字节],而NULL不占用空间.

BBED> p  *kdbr[0]
rowdata[6]
----------
ub1 rowdata[6]                              @8145     0x2c

BBED> x /r
rowdata[6]                                  @8145
----------
flag@8145: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8146: 0x01
cols@8147:    2

col    0[2] @8148:  0xc1  0x02
col   1[36] @8151:  0x00  0x54  0x00  0x01  0x02  0x0c  0x80  0x00  0x00  0x02
                    0x00  0x00  0x00  0x01  0x00  0x00  0x01  0xdd  0xf4  0x73 
                    0x00  0x10  0x09  0x00  0x00  0x00  0x00  0x00  0x00  0x00  
                    0x00  0x00  0x00  0x00  0x00  0x00

SQL> alter system dump datafile 4 block 1438;
System altered.

--看看转储文件:
Block header dump:  0x0100059e
 Object id on Block? Y
 seg/obj: 0x41d04  csc: 0x00.c010a962  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.020.00003023  0x00c006e2.1675.0a  --U-    2  fsc 0x0000.c010a966
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0100059e
data_block_dump,data header at 0x2a972c5264
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x2a972c5264
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f67
avsp=0x1f4e
tosp=0x1f4e
0xe:pti[0]  nrow=2  ffs=0
0x12:pri[0] ffs=0x1f6d
0x14:pri[1] ffs=0x1f67
block_row_dump:
tab 0, row 0, @0x1f6d
tl: 43 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [36]                    
 00 54 00 01 02 0c 80 00 00 02  
 00 00 00 01 00 00 01 dd f4 73  
 00 10 09 00 00 00 00 00 00 00  
 00 00 00 00 00 00
LOB
Locator:
  Length:        84(36)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.01.dd.f4.73
  Flags[ 0x02 0x0c 0x80 0x00 ]:
    Type: CLOB
    Storage: BasicFile
    Enable Storage in Row
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: VaringWidthReadWrite
  Inode:
    Size:     16
    Flag:     0x09 [ Valid DataInRow ]
    Future:   0x00 (should be '0x00')
    Blocks:   0
    Bytes:    0
    Version:  00000.0000000000
    Inline data[0]
Dump of memory from 0x0000002A972C71FC to 0x0000002A972C71FC
tab 0, row 1, @0x1f67
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  c1 03
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 1438 maxblk 1438

--11G版本转储的显示很清晰:

Header 占10bytes   : 00 54 00 01 02 0c 80 00 00 02
LOBID  占10bytes   : 00 00 00 01 00 00 01 dd f4 73
. LOB ID is a 10 byte number identifying individual instance of a LOB
. Allocated when LOB value is created including EMPTY_CLOB() etc
. Format is  where
      is a currently unknown 4-byte number (always 1)
      is a 6-byte number generated from sequence SYS.IDGEN1$

SQL> @16to10  01ddf473
16 to 10 DEC
------------
    31323251

SQL> select * from dba_sequences where sequence_owner='SYS' and sequence_name ='IDGEN1$';

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            IDGEN1$                                 1 1.0000E+28           50 N N       1000    31323651

Inode 占用16bytes
Body Length占2bytes: 00 10     => 等于16也就是36-前面的20字节(header的长度+lobid的长度)=16.
Flags      占2bytes: 09 00
LOB Length 占6bytes: 00 00 00 00 00 00      ==>empty_clob() 长度为0,clob自身长度为0
Version    占6bytes: 00 00 00 00 00 00


总结:
1.从以上可以看出,clob字段NULL与empty_clob()是不同的。
2.如果clob字段有信息,除了保存信息外额外要消耗36字节来保存相关信息。
3.其他问题看后续的帖子。

登录 后评论
下一篇
云栖号资讯小编
20035人浏览
2020-07-13
相关推荐
ORACLE常用数据库类型(转)
1055人浏览
2016-05-10 11:04:00
oracle易忘函数用法(5)
931人浏览
2017-10-12 10:10:00
Oracle 如何处理CLOB字段
947人浏览
2017-11-14 01:08:00
MySQL 常用命令大全
1121人浏览
2017-11-15 23:45:00
php操作Oralce
1016人浏览
2017-11-26 20:55:00
[20180416]clob的插入.txt
894人浏览
2018-04-16 10:23:46
0
0
0
730