CLOB的物理存储结构及语言编码详解
最近测试一个工具Database Migration for Unicode，功能是把非unicode编码存储数据的数据库转化为Unicode的数据库。在guide中提到一句，CLOB类型的列无需转换编码，这句话引起我的兴趣，为何别的字符类型char或者varchar2都需要编码转换，为什么CLOB不需要。在网上搜索，找到了DBSNAKE的这篇文章（http://dbsnake.com/2010/07/lob-characterset-endian.html）。里面提到了一篇Note [ID 257772.1],核心意思是，基本上从10g之后的CLOB的字符编码无论在何种CharacterSet下均为AL16UTF16。现在特来验证一下。
1. 在JA16EUC的数据库中创建测试用户及测试表(LOB存储为disable storage in row)
SELECT value FROM nls_database_parameters WHERE PARAMETER='NLS_CHARACTERSET'; VALUE ------------------------ JA16EUC CREATE TABLE tst.tab1(col1 CLOB) tablespace DATA lob (col1) store AS (tablespace DATA disable storage IN row nocache); INSERT INTO tst.tab1 VALUES(’ビジネス運営’); commit;
找出这一行存储的物理文件和block
SELECT dbms_rowid.rowid_block_number(rowid) bno,col1 FROM tst.tab1; BNO COL1 ---------- ------------------------------ 135 ビジネス運営 SELECT FILE_NAME,FILE_ID FROM dba_data_files; FILE_NAME FILE_ID -------------------------------------------------- ---------- /u01/app/oracle/oradata/euc2/system01.dbf 1 /u01/app/oracle/oradata/euc2/sysaux01.dbf 2 /u01/app/oracle/oradata/euc2/undotbs01.dbf 3 /u01/app/oracle/oradata/euc2/users01.dbf 4 /u01/app/oracle/oradata/euc2/DATA.dbf 5 ALTER system dump datafile 5 block 135;
在udump里找到对应的trace文件，因为使用的是11gR2，所以路径略有不同
SELECT value FROM v$diag_info WHERE name='Default Trace File'; VALUE ---------------------------------------------------------------- /u01/app/oracle/diag/rdbms/euc2/euc2/trace/euc2_ora_6234.trc
在上述trace里找到
block_row_dump:
tab 0, row 0, @0x1f80
tl: 24 fb: –H-FL– lb: 0×1 cc: 1
col 0: [20] 00 54 00 01 02 08 80 00 00 02 00 00 00 01 00 00 00 2d c8 b5 –>这里后10个字节就是LobID
LOB
Locator:
Length: 84(20)
Version: 1
Byte Length: 2
LobID: 00.00.00.01.00.00.00.2d.c8.b5
Flags[ 0x02 0x08 0x80 0x00 ]:
Type: CLOB
Storage: BasicFile
Disable Storage in Row
Characterset Format: IMPLICIT –>可以发现这里并没有写明CLOB字段所使用的编码
Partitioned Table: No
Options: VaringWidthReadWrite
这里就需要介绍一下LOB在enable还是disable storage in row这两种模式下的存储格式。
先看一下我们这个含有LOB的TAB1表有哪些相关的segment：
SELECT SEGMENT_NAME,SEGMENT_TYPE FROM dba_segments WHERE OWNER='TST'; SEGMENT_NAME SEGMENT_TYPE ---------------------------------------- --------------- TAB1 TABLE SYS_IL0000017724C00001$$ LOBINDEX SYS_LOB0000017724C00001$$ LOBSEGMENT
背景知识，LOB是Large Object（大对象）的简称，包括CLOB，NCLOB，BLOB，BFILE。这里我们讨论的只是CLOB。创建每一个LOB列除了表中的一个column外，另外同时还创建了两个物理结构：LOB index和LOB segment。
首先，需要了解的是在默认情况下LOB segment是和其所在的table存储在一起的，即in-line。而另外一种，LOB的访问是通过LOB locator来访问LOB index，LOB index再指向真正的物理存储数据的地方即LOB segment里的chunk。LOB locator就是指向LOB index的Key。
接下来先看一下Enable storage in row的方式下LOB的存储。这里分为两种情况，小于4000字节和大于4000字节。
LOB字段的locator加Inode加LOB的内容总长度小于4000字节时：
| Col1 | LOB Col |
| 1 | Locator1，Inode 1，LOB data 1 |
| 2 | Locator2，Inode 2，LOB data 2 |
这里LOB字段的存储是按如下的格式：
2字节的LOB locator长度（除这两个长度字节外）
2字节的LOB locator structure版本
4字节的FLAG
2字节的字符集里字符的长度
10字节的LOB ID
16字节的inode
之后就是LOB data，所以这里in-line的情况下理论上最多能存储4000-2-2-4-2-10-16=3964字节。
其他情况：
| Col1 | LOB Col | LOB Col | |
| 1 | Locator1，Inode 1 | ——–> | LOB data 1 |
| 2 | Locator2，Inode 2 | ——–> | LOB data 2 |
就是说即使Enable storage in row，LOB字段在LOB的控制信息（即Locator加Inode）长度加LOB data长度大于4000字节的时候，LOB data也是不存储在row内部的。在表里LOB字段的第36-84字节存储的是LOB data的前12个chunk的RDBA。如果12个不够的情况下，这里存放的不是chunk的RDBA，而是LOB index的RDBA。
另外，在Disable storage in row的方式下，数据都是这么存储的：
| Col1 | LOB Col | LOB Index | LOB Storage | ||
| 1 | Locator1 | ——> | Inode 1 | ——> | LOB data 1 |
| 2 | Locator2 | ——> | Inode 2 | ——> | LOB data 2 |
LOB index是一个类似于unique index的结构。这里我们把LOB index的叶子块的信息dump出来。
先找出TAB1的OBJ#：
SELECT object_id FROM dba_objects WHERE object_name='TAB1'; OBJECT_ID ---------- 17724 再找出TAB1里的LOB INDEX的ind#： SELECT IND# from lob$ where OBJ#='17724'; IND# ---------- 17726
把这个index的tree给dump出来：
ALTER session SET events 'immediate trace name treedump level 17726'; SELECT value FROM v$diag_info WHERE name='Default Trace File'; VALUE ------------------------------------------------------------- /u01/app/oracle/diag/rdbms/euc2/euc2/trace/euc2_ora_11704.trc
从上面这个trace中找到：
—– begin tree dump
leaf: 0×1400093 20971667 (0: nrow: 1 rrow: 1)
—– end tree dump
解释：前两列分别是RDBA的十六进制和十进制地址，由此可以得出leaf block的number。Nrow 是行数，如果是branch block的话就是child nodes的个数。
SELECT DBMS_UTILITY.data_block_address_block(TO_NUMBER(LTRIM('1400093'),'xxxxxxxx')) BNO FROM dual; BNO ---------- 147 ALTER system dump datafile 5 block 147;
得到50个字节的LOB index
row#0[7982] flag: ——, lock: 2, len=50, data:(32):
00 20 03 00 00 00 00 00 00 0c 00 00 00 00 00 01 01 40 00 8f 00 00 00 00 00
00 00 00 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 2d c8 b5
col 1; len 4; (4): 00 00 00 00
LOB index的结构是：
长度为50个byte，具体组成如下：
行头flag（1个byte）
lock byte（1个byte）
lob index key ( 32个byte，要么由16个byte的lob inode+4个RDBA组成，要么由8个RDBA组成。Disable storage in row的情况下是前者)
lob id的长度（1个byte）＋ lob id（10个byte）
page number的长度（1个byte）
page number（4个byte）
Inode的结构是：
2字节的in-line的data的长度
1字节的inode标志位：1代表这是个有效的lob，2代表这个inode在LOB index里，3代表这个lob是个有效的lob，而且在LOB index里
1字节的预留位
4字节full oracle blocks数量
2字节最后一个page里有多少byte的数据
6字节的LOB version，0×0001.00000000的形式
所以这里01 40 00 8f就是第一个chunk的RDBA的地址。
SELECT DBMS_UTILITY.data_block_address_block(TO_NUMBER(LTRIM('0140008f'),'xxxxxxxx')) BNO FROM dual; BNO ---------- 143 ALTER system dump datafile 5 block 143;
可以从trace中得到CLOB字段的实际存储的物理格式：
Long field block dump:
Object Id 17725
LobId: 00010002DC8B5 PageNo 0
Version: 0×0000.00000000 pdba: 20971656
30 d3 30 b8 30 cd 30 b9 90 4b 55 b6 00 20 00 20 00 20 00 20 00 20 00 20
我们验证一下这里的十六进制码到底跟哪个CharSet里的文字的物理格式一样：
CREATE TABLE aaa (col1 varchar2(100),col2 nvarchar2(100)); INSERT INTO aaa VALUES ('ビジネス運営','ビジネス運営'); SELECT dump(col1,1016),dump(col2,1016) FROM aaa; DUMP(COL1,1016) -------------------------------------------------------------------------------- DUMP(COL2,1016) -------------------------------------------------------------------------------- Typ=1 Len=12 CharacterSet=JA16EUC: a5,d3,a5,b8,a5,cd,a5,b9,b1,bf,b1,c4 Typ=1 Len=12 CharacterSet=AL16UTF16: 30,d3,30,b8,30,cd,30,b9,90,4b,55,b6
可见CLOB的物理存储格式并不是JA16EUC，而是AL16UTF16。
在研究这个东西的过程中得到了崔华（http://dbsnake.com）的大力帮助，在这里表示感谢。
参考：
DSI 402e：P52-P57，P250-P254
http://dbsnake.com/2010/07/some-lob-internal.html
全文PDF版本（排版好看很多）在这里 CLOB_Storage_Charset.pdf
不错的研究。打破砂锅问到底的态度是一定需要的。