[20130301]clob字段的empty_clob与NULL.txt
工作需要,开始研究一下clob字段.看看函数empty_clob()与null的区别.
1.建立测试环境:
总结：
1.从以上可以看出，clob字段NULL与empty_clob()是不同的。
2.如果clob字段有信息，除了保存信息外额外要消耗36字节来保存相关信息。
3.其他问题看后续的帖子。
工作需要,开始研究一下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.其他问题看后续的帖子。
