[20211229]sql语句包含中文保存clob的编码问题.txt

来源：这里教程网时间：2026-03-03 17:22:09 作者：

[20211229]sql语句包含中文保存clob的编码问题.txt --//昨天优化一条sql语句,无意中在toad下发现v$sqlarea视图的sql_fulltext保存汉字使用的编码是unicode。 --//比如病人 unicode编码是 c575 ba4e --// zhs16gbk编码是 b2a1 c8cb --// utf-8编码是 efbb bfe7 9785 e4ba ba --//注：utf-8 开头ef 是否某种特殊标识，我不是很清楚 0xef = 239 --//确定编码在vim下移动到字符,按ga,在提示行显示编码.unicode,utf-8编码文件我使用记事本建立的. 1.环境: SCOTT@book> @ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production $ env | grep -i lang NLS_LANG=AMERICAN_AMERICA.ZHS16GBK 2.测试: SCOTT@book> select deptno --病人 2 from dept; DEPTNO ---------- 10 20 30 40 --//从来没有注意putty下无法使用copy and paste中文，先放一放，先看看这个问题。 SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ------------------- ----------- 4052986859 c3xhwymst7azb 0 109547 2913917002 2021-12-30 11:04:56 16777216 SCOTT@book> select sql_fulltext from v$sqlarea where sql_id='c3xhwymst7azb'; SQL_FULLTEXT -------------------- select deptno --病人 from dept SCOTT@book> select dump(to_char(sql_fulltext),16) from v$sqlarea where sql_id='c3xhwymst7azb'; DUMP(TO_CHAR(SQL_FULLTEXT),16) ------------------------------------------------------------------------------------------------------ Typ=1 Len=30: 73,65,6c,65,63,74,20,64,65,70,74,6e,6f,20,2d,2d,b2,a1,c8,cb,a,66,72,6f,6d,20,64,65,70,74 - - ~~~~~~~~~~~ --//73,65,6c,65,63,74,20,64,65,70,74,6e,6f,20,2d,2d,b2,a1,c8,cb,a,66,72,6f,6d,20,64,65,70,74 = select deptno --病人from dept --//如果在toad下执行： select sql_fulltext from v$sqlarea where sql_id='c3xhwymst7azb'; --//查看clob的16进制： 730065006C00650063007400200064006500700074006E006F0020002D002D00C575BA4E0A00660072006F006D0020006400650070007400 ~~~~~~~~--//使用unicode编码。 --//是否意味者实际上保存在clob的编码采用的是unicode编码呢？ 3.导入测试表并分析： SCOTT@book> create table tx as select sql_id,sql_fulltext from v$sqlarea where sql_id='c3xhwymst7azb'; Table created. SCOTT@book> select rowid,tx.sql_id from tx ; ROWID SQL_ID ------------------ ------------- AABQsUAAEAAAAKzAAA c3xhwymst7azb SCOTT@book> @ rowid AABQsUAAEAAAAKzAAA OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 330516 4 691 0 0x10002B3 4,691 alter system dump datafile 4 block 691 ; SCOTT@book> alter system dump datafile 4 block 691 ; System altered. SCOTT@book> @ ttt tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_31991.trc 4.查看转储文件内容： Block header dump: 0x010002b3 Object id on Block? Y seg/obj: 0x50b14 csc: 0x03.1e889fd9 itc: 3 flg: E typ: 1 - DATA brn: 0 bdba: 0x10002b0 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0003.1e889fd9 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 bdba: 0x010002b3 data_block_dump,data header at 0x7fd33985827c =============== tsiz: 0x1f80 hsiz: 0x14 pbl: 0x7fd33985827c 76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1f12 avsp=0x1efe tosp=0x1efe 0xe:pti[0] nrow=1 offs=0 0x12:pri[0] offs=0x1f12 block_row_dump: tab 0, row 0, @0x1f12 tl: 110 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [13] 63 33 78 68 77 79 6d 73 74 37 61 7a 62 col 1: [92] 00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 06 74 c5 f7 00 48 09 00 00 00 00 00 00 38 00 00 00 00 00 01 00 73 00 65 00 6c 00 65 00 63 00 74 00 20 00 64 00 65 00 70 00 74 00 6e 00 6f 00 20 00 2d 00 2d 75 c5 4e ba 00 0a 00 66 00 72 00 6f 00 6d 00 20 00 64 00 65 00 70 00 74 LOB Locator: Length: 84(92) Version: 1 Byte Length: 2 LobID: 00.00.00.01.00.00.06.74.c5.f7 Flags[ 0x02 0x0c 0x80 0x00 ]: Type: CLOB Storage: BasicFile Enable Storage in Row Characterset Format: IMPLICIT Partitioned Table: No Options: VaringWidthReadWrite Inode: Size: 72 Flag: 0x09 [ Valid DataInRow ] Future: 0x00 (should be '0x00') Blocks: 0 Bytes: 56 Version: 00000.0000000001 Inline data[56] Dump of memory from 0x00007FD33985A1C4 to 0x00007FD33985A1FC 7FD33985A1C0 65007300 65006C00 74006300 [.s.e.l.e.c.t] 7FD33985A1D0 64002000 70006500 6E007400 20006F00 [. .d.e.p.t.n.o. ] 7FD33985A1E0 2D002D00 BA4EC575 66000A00 6F007200 [.-.-u.N....f.r.o] ~~~~~~~~=>确实是unicode编码 7FD33985A1F0 20006D00 65006400 74007000 [.m. .d.e.p.t] end_of_block_dump 4.通过bbed观察看看: SCOTT@book> alter system checkpoint ; System altered. BBED> set dba 4,691 DBA 0x010002b3 (16777907 4,691) BBED> x /rcx *kdbr[0] rowdata[0] @8078 ---------- flag@8078: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8079: 0x00 cols@8080: 2 col 0[13] @8081: c3xhwymst7azb col 1[92] @8095: 0x00 0x54 0x00 0x01 0x02 0x0c 0x80 0x00 0x00 0x02 0x00 0x00 0x00 0x01 0x00 0x00 0x06 0x74 0xc5 0xf7 0x00 0x48 0x09 0x00 0x00 0x00 0x00 0x00 0x00 0x38 0x00 0x00 0x00 0x00 0x00 0x01 0x00 0x73 0x00 0x65 0x00 0x6c 0x00 0x65 0x00 0x63 0x00 0x74 0x00 0x20 0x00 0x64 0x00 0x65 0x00 0x70 0x00 0x74 0x00 0x6e 0x00 0x6f 0x00 0x20 0x00 0x2d 0x00 0x2d 0x75 0xc5 0x4e 0xba 0x00 0x0a 0x00 0x66 ~~~~~~~~~~~~~~~~~~~~~~ 0x00 0x72 0x00 0x6f 0x00 0x6d 0x00 0x20 0x00 0x64 0x00 0x65 0x00 0x70 0x00 0x74 --//昏，顺序颠倒过来。 5.看看securefile clob的保存情况呢?估计保存信息编码不会变。 SCOTT@book> CREATE TABLE ty(sql_id varchar(13) ,sql_fulltext CLOB ) LOB(sql_fulltext) STORE AS SECUREFILE; Table created. SCOTT@book> insert into ty select * from tx; 1 row created. SCOTT@book> commit ; Commit complete. SCOTT@book> select rowid,ty.sql_id from ty ; ROWID SQL_ID ------------------ ------------- AABQsXAAEAAAAK8AAA c3xhwymst7azb SCOTT@book> @ rowid AABQsXAAEAAAAK8AAA OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 330519 4 700 0 0x10002BC 4,700 alter system dump datafile 4 block 700 ; SCOTT@book> alter system checkpoint ; System altered. --//bbed观察： BBED> set dba 4,700 DBA 0x010002bc (16777916 4,700) BBED> x /rcx *kdbr[0] rowdata[0] @8084 ---------- flag@8084: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8085: 0x01 cols@8086: 2 col 0[13] @8087: c3xhwymst7azb col 1[86] @8101: 0x00 0x54 0x00 0x01 0x02 0x0c 0x80 0x80 0x00 0x02 0x00 0x00 0x00 0x01 0x00 0x00 0x06 0x74 0xc5 0xfa 0x00 0x42 0x48 0x90 0x00 0x3c 0x00 0x00 0x38 0x01 0x00 0x73 0x00 0x65 0x00 0x6c 0x00 0x65 0x00 0x63 0x00 0x74 0x00 0x20 0x00 0x64 0x00 0x65 0x00 0x70 0x00 0x74 0x00 0x6e 0x00 0x6f 0x00 0x20 0x00 0x2d 0x00 0x2d 0x75 0xc5 0x4e 0xba ~~~~~~~~~~~~~~~~~~~~~~~ 0x00 0x0a 0x00 0x66 0x00 0x72 0x00 0x6f 0x00 0x6d 0x00 0x20 0x00 0x64 0x00 0x65 0x00 0x70 0x00 0x74 SCOTT@book> @ ti New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_31991_0001.trc SCOTT@book> alter system dump datafile 4 block 700 ; System altered. Block header dump: 0x010002bc Object id on Block? Y seg/obj: 0x50b17 csc: 0x03.1e88a461 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x10002b8 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000b.01d.00000688 0x00c001c1.01db.06 --U- 1 fsc 0x0000.1e88a464 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 bdba: 0x010002bc data_block_dump,data header at 0x7fd339858264 =============== tsiz: 0x1f98 hsiz: 0x14 pbl: 0x7fd339858264 76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1f30 avsp=0x1f1c tosp=0x1f1c 0xe:pti[0] nrow=1 offs=0 0x12:pri[0] offs=0x1f30 block_row_dump: tab 0, row 0, @0x1f30 tl: 104 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [13] 63 33 78 68 77 79 6d 73 74 37 61 7a 62 col 1: [86] 00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 06 74 c5 fa 00 42 48 90 00 3c 00 00 38 01 00 73 00 65 00 6c 00 65 00 63 00 74 00 20 00 64 00 65 00 70 00 74 00 6e 00 6f 00 20 00 2d 00 2d 75 c5 4e ba 00 0a 00 66 00 72 00 6f 00 6d 00 20 00 64 00 65 00 70 00 74 LOB Locator: Length: 84(86) Version: 1 Byte Length: 2 LobID: 00.00.00.01.00.00.06.74.c5.fa Flags[ 0x02 0x0c 0x80 0x80 ]: Type: CLOB Storage: SecureFile Characterset Format: IMPLICIT Partitioned Table: No Options: VaringWidthReadWrite SecureFile Header: Length: 66 Old Flag: 0x48 [ DataInRow SecureFile ] Flag 0: 0x90 [ INODE Valid ] Layers: Lengths Array: INODE:60 INODE: 00 00 38 01 00 73 00 65 00 6c 00 65 00 63 00 74 00 20 00 64 00 65 00 70 00 74 00 6e 00 6f 00 20 00 2d 00 2d 75 c5 4e ba ~~~~~~~~~~~~ 00 0a 00 66 00 72 00 6f 00 6d 00 20 00 64 00 65 00 70 00 74 end_of_block_dump --//这也是我不主张在生产系统使用clob类型的原因。clob类型可能给根据字符集,选择合理的编码. --//如果你是英文字符为主的话,每次英文字符消耗2个字节,如果里面的编码是utf-8的话,消耗空间更大. --//blob类型没有这类问题,原样保存。