ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20131116]12c的EXTENDED VARCHAR2与CLOB字段.txt

[20131116]12c的EXTENDED VARCHAR2与CLOB字段.txt

原创 Linux操作系统 作者:lfree 时间:2013-11-18 09:45:07 0 删除 编辑
[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.>.<.h]
01E826830 6E006900 3E007400 21003C00 43005B00  [.i.n.t.>.<.!.[.C]
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  [.].].>.<./.h.i.n]
01E8268A0 3E007400 2F003C00 75006F00 6C007400  [.t.>.<./.o.u.t.l]
01E8268B0 6E006900 5F006500 61006400 61007400  [.i.n.e._.d.a.t.a]
01E8268C0 3C003E00 6F002F00 68007400 72006500  [.>.<./.o.t.h.e.r]
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.缺乏一些资料,许多仅仅是推测。

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2600
  • 访问量
    6374690