[20231013]CLOB类型的编码问题.txt

[20231013]CLOB类型的编码问题.txt --//昨天想看一串unicode的内容,看了一些链接: https://smarttechways.com/2023/05/29/nls_nchar_characterset-and-nls_characterset-define-in-oracle/ National Character Set(NLS_NCHAR_CHARACTERSET) defines the encoding of NCHAR, NVARCHAR2, and NCLOB columns and is in 9i and up consistently Unicode. eg. AL16UTF16 国家字符集(NLS_NCHAR_CHARACTERSET)定义了NCHAR、NVARCHAR2和NCLOB列的编码，并在9i中表示和一致的统一码。例如AL16UTF16 Character Set(NLS_CHARACTERSET) defines the encoding of CHAR, VARCHAR2, LONG, and CLOB columns, these can also be used for storing Unicode. eg AL32UTF8 or UTF8 字符集(NLS_CHARACTERSET)定义了CHAR、VARCHAR2、LONG和CLOB列的编码，也可以使用它们用于存储Unicode。如AL32UTF8或UTF8 --//注:感觉这里不对,clob列的编码好像也是AL16UTF16.我给测试看看是否这样的情况. The national Character set (NLS_NCHAR_CHARACTERSET) is used for data stored in NCHAR, NVARCHAR2, and NCLOB datatypes and is a character set that is defined in addition to the (standard) database character set (NLS_CHARACTERSET), which is used for CHAR, VARCHAR2, LONG and CLOB datatypes. 国家字符集(NLS_NCHAR_CHARACTERSET)用于存储在NCHAR、NVARCHAR2和NCLOB数据类型和中的数据，和是在(标准)数据库字符集 (NLS_CHARACTERSET)之外定义的字符集，即用于CHAR、VARCHAR2、LONG和CLOB数据类型。 1.环境: $ env | grep NLS NLS_LANG=AMERICAN_AMERICA.ZHS16GBK NLS_TIME_TZ_FORMATx=HH24.MI.SSXFF TZH:TZM NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS 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 SCOTT@book> select parameter,value from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET'); PARAMETER              VALUE ---------------------- ---------- NLS_CHARACTERSET       ZHS16GBK NLS_NCHAR_CHARACTERSET AL16UTF16 2.测试: SCOTT@book> CREATE TABLE t1 ( id number,vc varchar2(30),nc nvarchar2(30),cc clob); Table created. SCOTT@book> insert into t1 values(1,'a测试中文a','a测试中文b','a测试中文c'); 1 row created. SCOTT@book> commit ; Commit complete. SCOTT@book> alter system checkpoint; System altered. SCOTT@book> select rowid from t1; ROWID ------------------ AAAWsoAAEAAAAKvAAB SCOTT@book> @ rowid AAAWsoAAEAAAAKvAAB     OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------      92968          4        687          1  0x10002AF           4,687                alter system dump datafile 4 block 687 ; SCOTT@book> alter system dump datafile 4 block 687 ; System altered. 3.检查跟踪文件: Block header dump:  0x010002af  Object id on Block? Y  seg/obj: 0x16b28  csc: 0x03.1873920b  itc: 2  flg: E  typ: 1 - DATA      brn: 0  bdba: 0x10002a8 ver: 0x01 opc: 0      inc: 0  exflg: 0  Itl           Xid                  Uba         Flag  Lck        Scn/Fsc 0x01   0x000a.004.0000d5b4  0x00c00bf1.2efa.1e  --U-    1  fsc 0x0000.18739220 0x02   0x000a.00e.0000d5dd  0x00c00bf1.2efa.1d  --U-    1  fsc 0x0043.1873920d bdba: 0x010002af data_block_dump,data header at 0x7fe7c95a4264 =============== tsiz: 0x1f98 hsiz: 0x16 pbl: 0x7fe7c95a4264      76543210 flag=-------- ntab=1 nrow=2 frre=-1 fsbo=0x16 fseo=0x1f04 avsp=0x1eee tosp=0x1f33 0xe:pti[0]  nrow=2  offs=0 0x12:pri[0] offs=0x1f53 0x14:pri[1] offs=0x1f04 block_row_dump: tab 0, row 0, @0x1f53 tl: 2 fb: --HDFL-- lb: 0x2 tab 0, row 1, @0x1f04 tl: 79 fb: --H-FL-- lb: 0x1  cc: 4 col  0: [ 2]  c1 02 col  1: [10]  61 b2 e2 ca d4 d6 d0 ce c4 61 col  2: [12]  00 61 6d 4b 8b d5 4e 2d 65 87 00 62 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ col  3: [48]  00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 06 72 79 80 00 1c 09 00 00  00 00 00 00 0c 00 00 00 00 00 01 00 61 6d 4b 8b d5 4e 2d 65 87 00 63                                   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ LOB Locator:   Length:        84(48)   Version:        1   Byte Length:    2   LobID: 00.00.00.01.00.00.06.72.79.80   Flags[ 0x02 0x0c 0x80 0x00 ]:     Type: CLOB     Storage: BasicFile     Enable Storage in Row     Characterset Format: IMPLICIT     Partitioned Table: No     Options: VaringWidthReadWrite   Inode:     Size:     28     Flag:     0x09 [ Valid DataInRow ]     Future:   0x00 (should be '0x00')     Blocks:   0     Bytes:    12     Version:  00000.0000000001     Inline data[12] Dump of memory from 0x00007FE7C95A61AB to 0x00007FE7C95A61B7 --//61 b2 e2 ca d4 d6 d0 ce c4 61 = a测试中文a , varchar2能对上. --//注意看下划线可以发现nvarchar2,clob的编码一样的.都是unicode big endian编码. $ echo fffe 00 61 6d 4b 8b d5 4e 2d 65 87 00 63 | xxd -r -p  > a.txt $ file a.txt a.txt: Big-endian UTF-16 Unicode character data, with no line terminators --//再上传到windows的机器,使用记事本打开就可以验证编码就是Big-endian UTF-16 Unicode character data. --//我有点无法理解,intel系列机器不是采用Little endian吗,为什么nvarchar2,COLB使用的却是Big endian. SCOTT@book> select * from V$TRANSPORTABLE_PLATFORM; PLATFORM_ID PLATFORM_NAME                     ENDIAN_FORMAT ----------- --------------------------------- --------------           1 Solaris[tm] OE (32-bit)           Big           2 Solaris[tm] OE (64-bit)           Big           7 Microsoft Windows IA (32-bit)     Little          10 Linux IA (32-bit)                 Little           6 AIX-Based Systems (64-bit)        Big           3 HP-UX (64-bit)                    Big           5 HP Tru64 UNIX                     Little           4 HP-UX IA (64-bit)                 Big          11 Linux IA (64-bit)                 Little          15 HP Open VMS                       Little           8 Microsoft Windows IA (64-bit)     Little           9 IBM zSeries Based Linux           Big          13 Linux x86 64-bit                  Little ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~                  16 Apple Mac OS                      Big          12 Microsoft Windows x86 64-bit      Little          17 Solaris Operating System (x86)    Little          18 IBM Power Based Linux             Big          19 HP IA Open VMS                    Little          20 Solaris Operating System (x86-64) Little          21 Apple Mac OS (x86-64)             Little 20 rows selected.