[20131116]12c的EXTENDED VARCHAR2与CLOB字段.txt
oracle 12c以前如果字符串长度超过4000，必须使用blob或者clob类型。12c开始支持超过4000的字符串长度，提高一些应用的灵活性，
达到32K，避免一些字段定义为clob，blob类型，提高处理能力。
要支持这个特性，要执行 @?/rdbms/admin/utl32k.sql升级步骤。如果存在pdb库，还必须升级PDB$SEED，以及PDB数据库。
参考链接：http://space.itpub.net/267265/viewspace-772855/
如果字符串长度超过4000,oracle会如何存储的呢？自己做一些测试看看。
1.建立测试环境 ：
--我的安装环境:
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> select * from V$NLS_PARAMETERS where parameter='NLS_NCHAR_CHARACTERSET';
PARAMETER VALUE CON_ID
----------------------- ---------- ----------
NLS_NCHAR_CHARACTERSET AL16UTF16 0
--以前写过一篇blog，提到clob，保存的信息使用unicode格式，这样1个英文字符占用2个字节。
--链接：http://space.itpub.net/267265/viewspace-755470/
--看看varchar2长度>4000的情况如下:
SCOTT@test01p> select * from user_lobs;
no rows selected
create table t1 ( id number,a varchar2(5000),b clob );
insert into t1 values (1,lpad('a',5000,'a'),lpad('b',5000,'b'));
commit;
SCOTT@test01p> select table_name,segment_name from user_lobs;
TABLE_NAME SEGMENT_NAME
---------- ------------------------------
T1 SYS_LOB0000093533C00002$$
T1 SYS_LOB0000093533C00003$$
--可以发现表使用两个段，可以推测大于4000个字符时，字符使用clob相似的存储方式。看看对比就很清楚了。
SCOTT@test01p> column a noprint
SCOTT@test01p> column b noprint
SCOTT@test01p> select rowid,t1.* from t1;
ROWID ID
------------------ ----------
AAAW1dAAJAAAADHAAA 1
SCOTT@test01p> @lookup_rowid AAAW1dAAJAAAADHAAA
OBJECT FILE BLOCK ROW DBA
---------- ---------- ---------- ---------- --------------------
93533 9 199 0 9,199
SCOTT@test01p> alter system checkpoint ;
System altered.
--最好做这步，这样转储的块的内容才是最新的。
SCOTT@test01p> alter system dump datafile 9 block 199;
System altered.
Block header dump: 0x024000c7
Object id on Block? Y
seg/obj: 0x16d5d csc: 0x00.446905 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x24000c0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.005.00000b68 0x014052cd.018f.14 --U- 1 fsc 0x0000.00446909
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x024000c7
data_block_dump,data header at 0x1e826064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x1e826064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f44
avsp=0x1f30
tosp=0x1f30
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f44
block_row_dump:
tab 0, row 0, @0x1f44
tl: 84 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [38]
00 54 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 77 f5 63 00 12 40 90 00
0c 21 00 13 88 01 00 01 02 40 00 d3 01
LOB
Locator:
Length: 84(38)
Version: 1
Byte Length: 1
LobID: 00.00.00.01.00.00.00.77.f5.63
Flags[ 0x01 0x0c 0x00 0x80 ]:
Type: BLOB
Storage: SecureFile
Characterset Format: IMPLICIT
Partitioned Table: No
Options: ReadWrite
SecureFile Header:
Length: 18
Old Flag: 0x40 [ SecureFile ]
Flag 0: 0x90 [ INODE Valid ]
Layers:
Lengths Array: INODE:12
INODE:
21 00 13 88 01 00 01 02 40 00 d3 01
col 2: [38]
00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 00 77 f5 64 00 12 40 90 00
0c 21 00 27 10 01 00 01 02 40 00 eb 02
LOB
Locator:
Length: 84(38)
Version: 1
Byte Length: 2
LobID: 00.00.00.01.00.00.00.77.f5.64
Flags[ 0x02 0x0c 0x80 0x80 ]:
Type: CLOB
Storage: SecureFile
Characterset Format: IMPLICIT
Partitioned Table: No
Options: VaringWidthReadWrite
SecureFile Header:
Length: 18
Old Flag: 0x40 [ SecureFile ]
Flag 0: 0x90 [ INODE Valid ]
Layers:
Lengths Array: INODE:12
INODE:
21 00 27 10 01 00 01 02 40 00 eb 02
end_of_block_dump
End dump data blocks tsn: 3 file#: 9 minblk 199 maxblk 199
--了解securefile lob如何存储，参考链接:http://www.juliandyke.com/Presentations/LOBInternals.ppt
--可以发现12c缺省的clob，选择类型的是securefile，与10g,11g不同，10g，11g下缺省是basicfile.
--上下对比可以发现：字段a选择的是type=BLOB,而字段b的TYPE=clob
--字段a的Options: ReadWrite ，字段b的 Options: VaringWidthReadWrite 。
--字段a的INODE: 21 00 13 88 01 00 01 02 40 00 d3 01，很容易从中间猜测02 40 00 d3是块地址.后面表示# Blocks in extent
--字段b的INODE: 21 00 27 10 01 00 01 02 40 00 eb 02 ,很容易从中间猜测02 40 00 eb是块地址.后面表示# Blocks in extent
--再乱猜测看看：
SCOTT@test01p> @10to16 5000
10 to 16 HEX
--------------
1388
SCOTT@test01p> @10to16 10000
10 to 16 HEX
--------------
2710
-- 0x1388 表示字段a的长度，0x2710表示字段b的长度。其他不好乱猜测，^_^。
SCOTT@test01p> @dfb 024000d3
RFILE# BLOCK#
---------- ----------
9 211
'ALTERSYSTEMDUMPDATAFILE'||DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(TO_NUMBER('024000D3','XXXXXXXXXXXXXXXX'))||'BLOCK'||
--------------------------------------------------------------------------------------------------------------------
alter system dump datafile 9 block 211 ;
SCOTT@test01p> @dfb 024000eb
RFILE# BLOCK#
---------- ----------
9 235
'ALTERSYSTEMDUMPDATAFILE'||DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(TO_NUMBER('024000EB','XXXXXXXXXXXXXXXX'))||'BLOCK'||
--------------------------------------------------------------------------------------------------------------------
alter system dump datafile 9 block 235 ;
--再转储这些块看看。
2.先转储datafile 9 block 211 ：
alter system dump datafile 9 block 211 ;
Block dump from disk:
buffer tsn: 3 rdba: 0x024000d3 (9/211)
scn: 0x0.44695d seq: 0x01 flg: 0x04 tail: 0x695d0601
frmt: 0x02 chkval: 0x4a39 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001E826000 to 0x000000001E828000
01E826000 0000A206 024000D3 0044695D 04010000 [......@.]iD.....]
01E826010 00004A39 00000005 00016D5E 0044695D [9J......^m..]iD.]
01E826020 00000000 00220001 FFFFFFFF 00050001 [......".........]
01E826030 00000B68 00000000 00000000 0000E000 [h...............]
01E826040 0044695D 00000000 00000000 004468E4 []iD..........hD.]
01E826050 00200000 01000000 77000000 000063F5 [.. ........w.c..]
01E826060 00000000 00000000 00000000 00000000 [................]
01E826070 00000000 00000000 00000000 00001388 [................]
01E826080 61616161 61616161 61616161 61616161 [aaaaaaaaaaaaaaaa]
Repeat 311 times
01E827400 61616161 61616161 00000000 00000000 [aaaaaaaa........]
01E827410 00000000 00000000 00000000 00000000 [................]
Repeat 189 times
01E827FF0 00000000 00000000 00000000 695D0601 [..............]i]
Block header dump: 0x024000d3
Object id on Block? Y
seg/obj: 0x16d5e csc: 0x00.44695d itc: 1 flg: E typ: 5 - LOCAL LOBS
fsl: 0 fnx: 0xffffffff ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.005.00000b68 0x00000000.0000.00 CBU- 0 scn 0x0000.0044695d
========
bdba [0x024000d3]
kdlich [000000001E82604C 56]
flg0 0x20 [ver=0 typ=data lock=n]
flg1 0x00
scn 0x0000.004468e4
lid 0000000100000077f563
rid 0x00000000.0000
kdlidh [000000001E826064 24]
flg2 0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
flg3 0x00
pskip 0
sskip 0
hash 0000000000000000000000000000000000000000
hwm 5000
spr 0
data [000000001E826080 52 8060]
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
...
61 61 61 61 61 61 61 61 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
....
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
--- end of lob block dump ---
End dump data blocks tsn: 3 file#: 9 minblk 211 maxblk 211
-- 312*16+8=5000。
3.再转储datafile 9 block 235 ：
alter system dump datafile 9 block 235 ;
Block dump from disk:
buffer tsn: 3 rdba: 0x024000eb (9/235)
scn: 0x0.446903 seq: 0x02 flg: 0x04 tail: 0x69030602
frmt: 0x02 chkval: 0x896b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001E826000 to 0x000000001E828000
01E826000 0000A206 024000EB 00446903 04020000 [......@..iD.....]
01E826010 0000896B 00000005 00016D60 00446903 [k.......`m...iD.]
01E826020 00000000 00220001 FFFFFFFF 00050001 [......".........]
01E826030 00000B68 00000000 00000000 00004000 [h............@..]
01E826040 00000000 00000000 00000000 00446903 [.............iD.]
01E826050 00200000 01000000 77000000 000064F5 [.. ........w.d..]
01E826060 00000000 00000000 00000000 00000000 [................]
01E826070 00000000 00000000 00000000 00001F7C [............|...]
01E826080 62006200 62006200 62006200 62006200 [.b.b.b.b.b.b.b.b]
Repeat 502 times
01E827FF0 62006200 62006200 62006200 69030602 [.b.b.b.b.b.b...i]
Block header dump: 0x024000eb
Object id on Block? Y
seg/obj: 0x16d60 csc: 0x00.446903 itc: 1 flg: E typ: 5 - LOCAL LOBS
fsl: 0 fnx: 0xffffffff ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.005.00000b68 0x00000000.0000.00 -B-- 0 fsc 0x0000.00000000
========
bdba [0x024000eb]
kdlich [000000001E82604C 56]
flg0 0x20 [ver=0 typ=data lock=n]
flg1 0x00
scn 0x0000.00446903
lid 0000000100000077f564
rid 0x00000000.0000
kdlidh [000000001E826064 24]
flg2 0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
flg3 0x00
pskip 0
sskip 0
hash 0000000000000000000000000000000000000000
hwm 8060
spr 0
data [000000001E826080 52 8060]
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62
...
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62
--- end of lob block dump ---
End dump data blocks tsn: 3 file#: 9 minblk 235 maxblk 235
-- 503*16+12=8060.
-- 这个块仅仅保存8060的字符。还剩下的1940如何体现出来呢？
SCOTT@test01p> @10to16 1940
10 to 16 HEX
--------------
794
--再转储datafile 9 block 236 ：
SCOTT@test01p> alter system dump datafile 9 block 236 ;
System altered.
Block dump from disk:
buffer tsn: 3 rdba: 0x024000ec (9/236)
scn: 0x0.446903 seq: 0x02 flg: 0x04 tail: 0x69030602
frmt: 0x02 chkval: 0x8984 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001E826000 to 0x000000001E828000
01E826000 0000A206 024000EC 00446903 04020000 [......@..iD.....]
01E826010 00008984 00000005 00016D60 00446903 [........`m...iD.]
01E826020 00000000 00220001 FFFFFFFF 00050001 [......".........]
01E826030 00000B68 00000000 00000000 00004000 [h............@..]
01E826040 00000000 00000000 00000000 00446903 [.............iD.]
01E826050 00200000 01000000 77000000 000064F5 [.. ........w.d..]
01E826060 00000000 00000000 00000000 00000000 [................]
01E826070 00000000 00000000 00000000 00000794 [................]
01E826080 62006200 62006200 62006200 62006200 [.b.b.b.b.b.b.b.b]
Repeat 120 times
01E826810 62006200 29002200 5D005D00 3C003E00 [.b.b.".).].].>.
01E826820 68002F00 6E006900 3E007400 68003C00 [./.h.i.n.t.>.
01E826830 6E006900 3E007400 21003C00 43005B00 [.i.n.t.>.
01E826840 41004400 41005400 55005B00 45005300 [.D.A.T.A.[.U.S.E]
01E826850 4E005F00 28004C00 22004000 45005300 [._.N.L.(.@.".S.E]
01E826860 24004C00 41003700 45004200 43003100 [.L.$.7.A.B.E.1.C]
01E826870 46003400 20002200 55002200 40002200 [.4.F.". .".U.".@]
01E826880 53002200 4C004500 32002400 29002200 [.".S.E.L.$.2.".)]
01E826890 5D005D00 3C003E00 68002F00 6E006900 [.].].>.
01E8268A0 3E007400 2F003C00 75006F00 6C007400 [.t.>.
01E8268B0 6E006900 5F006500 61006400 61007400 [.i.n.e._.d.a.t.a]
01E8268C0 3C003E00 6F002F00 68007400 72006500 [.>.
01E8268D0 78005F00 6C006D00 00003E00 00000000 [._.x.m.l.>......]
01E8268E0 00000000 00000000 00000000 00000000 [................]
Repeat 368 times
01E827FF0 00000000 00000000 00000000 69030602 [...............i]
Block header dump: 0x024000ec
Object id on Block? Y
seg/obj: 0x16d60 csc: 0x00.446903 itc: 1 flg: E typ: 5 - LOCAL LOBS
fsl: 0 fnx: 0xffffffff ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.005.00000b68 0x00000000.0000.00 -B-- 0 fsc 0x0000.00000000
========
bdba [0x024000ec]
kdlich [000000001E82604C 56]
flg0 0x20 [ver=0 typ=data lock=n]
flg1 0x00
scn 0x0000.00446903
lid 0000000100000077f564
rid 0x00000000.0000
kdlidh [000000001E826064 24]
flg2 0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
flg3 0x00
pskip 0
sskip 0
hash 0000000000000000000000000000000000000000
hwm 1940
spr 0
data [000000001E826080 52 8060]
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62
...
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 22 00 29 00 5d 00 5d 00 3e
00 3c 00 2f 00 68 00 69 00 6e 00 74 00 3e 00 3c 00 68 00 69 00 6e 00 74 00 3e
00 3c 00 21 00 5b 00 43 00 44 00 41 00 54 00 41 00 5b 00 55 00 53 00 45 00 5f
00 4e 00 4c 00 28 00 40 00 22 00 53 00 45 00 4c 00 24 00 37 00 41 00 42 00 45
00 31 00 43 00 34 00 46 00 22 00 20 00 22 00 55 00 22 00 40 00 22 00 53 00 45
00 4c 00 24 00 32 00 22 00 29 00 5d 00 5d 00 3e 00 3c 00 2f 00 68 00 69 00 6e
00 74 00 3e 00 3c 00 2f 00 6f 00 75 00 74 00 6c 00 69 00 6e 00 65 00 5f 00 64
00 61 00 74 00 61 00 3e 00 3c 00 2f 00 6f 00 74 00 68 00 65 00 72 00 5f 00 78
00 6d 00 6c 00 3e 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
....
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
--- end of lob block dump ---
End dump data blocks tsn: 3 file#: 9 minblk 236 maxblk 236
--121*16+4 = 1940.
--字段b的INODE: 21 00 27 10 01 00 01 02 40 00 eb 02 ,最后的02仅仅推测表示前面的02 40 00 eb取连续的2块.
总结：
1.varchar2 大于4000以上使用blob一样的保存方式。
2.clob与blob不同，也许与NLS_NCHAR_CHARACTERSET有关，blob原样保存，而clob使用unicode编码，这样1个英文字符占用2个字符空间。
3.缺乏一些资料，许多仅仅是推测。