[20221012]简单探究nvarchar2数据类型存储.txt

--//自己对于nvarchar2数据类型不是很了解,简单探究看看.
--//我以前仅仅知道数字与字母占用2个字符,比如A ascii码65(0x41),如果采用nvarchar2,存储变成0x00 0x41.
--//另外nvarchar2数据类型我不知道是否与字符集有关,顺便也做一个探究.

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

CREATE TABLE t1
AS
   SELECT object_id
         ,object_name
         ,CAST (object_name AS NVARCHAR2 (128)) vobject_name
     FROM all_objects where rownum<=3;

SCOTT@book> alter table t1 modify VOBJECT_NAME  not null ;
Table altered.

update t1 set object_name='测试中文',vobject_name='测试中文' where rownum=1;
commit ;
alter system checkpoint;
alter system checkpoint;

SCOTT@book> select rowid from t1 where rownum=1;
ROWID
------------------
AABR50AAEAAABHjAAA

SCOTT@book> @ rowid AABR50AAEAAABHjAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
    335476          4       4579          0  0x10011E3           4,4579               alter system dump datafile 4 block 4579

2.通过bbed观察:
$ export NLS_LANG=AMERICAN_AMERICA.US7SACII
--//方便bbed查看中文汉字.

BBED> set dba   4,4579
        DBA             0x010011e3 (16781795 4,4579)
BBED> x /rncx *kdbr[1]
rowdata[44]                                 @8136
-----------
flag@8136: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8137: 0x00
cols@8138:    3

col    0[2] @8139: 46
col    1[7] @8142: I_USER1
col   2[14] @8150:  0x00  0x49  0x00  0x5f  0x00  0x55  0x00  0x53  0x00  0x45  0x00  0x52  0x00  0x31


BBED> x /rnxx *kdbr[1]
rowdata[44]                                 @8136
-----------
flag@8136: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8137: 0x00
cols@8138:    3

col    0[2] @8139: 46
col    1[7] @8142:  0x49  0x5f  0x55  0x53  0x45  0x52  0x31
col   2[14] @8150:  0x00  0x49  0x00  0x5f  0x00  0x55  0x00  0x53  0x00  0x45  0x00  0x52  0x00  0x31
--//你可以发现英文字符nvarchar2编码前面每个多了0x00,这样占用空间会加倍.

BBED> x /rncx *kdbr[0]
rowdata[0]                                  @8092
----------
flag@8092: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8093: 0x02
cols@8094:    3

col    0[2] @8095: 20
col    1[8] @8098: 测试中文
col    2[8] @8107:  0x6d  0x4b  0x8b  0xd5  0x4e  0x2d  0x65  0x87

BBED> x /rnxx *kdbr[0]
rowdata[0]                                  @8092
----------
flag@8092: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8093: 0x02
cols@8094:    3

col    0[2] @8095: 20
col    1[8] @8098:  0xb2  0xe2  0xca  0xd4  0xd6  0xd0  0xce  0xc4
col    2[8] @8107:  0x6d  0x4b  0x8b  0xd5  0x4e  0x2d  0x65  0x87
---//而中文汉字的编码nvarchar2有点奇怪.

3.我看了我以前建立测试数据库的脚本,执行如下:

# mkdir -p /mnt/ramdisk
# mount -t tmpfs -o size=8G tmpfs /mnt/ramdisk
# su - oracle
--//检查环境变量是否设置正确
$ env | grep -i oracle
USER=oracle
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib:/u01/app/oracle/product/11.2.0/db_1/oracm/lib:/lib:/usr/lib:/usr/local/lib:/lib64:/usr/lib64:/usr/local/lib64
ORACLE_BASE=/u01/app/oracle
PATH=.:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/NX/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/db_1/bin
HOME=/home/oracle
LOGNAME=oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

$ echo $NLS_LANG
AMERICAN_AMERICA.zhs16gbk

$ export ORACLE_SID=book

$ORACLE_HOME/bin/dbca -createDatabase -templateName General_Purpose.dbc -gdbName test -sid test -sysPassword oracle \
-systemPassword oracle -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -listeners LISTENER -sampleSchema  true -totalMemory 800 \
-databaseType MULTIPURPOSE -silent -datafileDestination /mnt/ramdisk

--//-characterSet ZHS16GBK -nationalCharacterSet AL16UTF16,我建立的生产系统也是使用这样的字符集.
--//感觉这个nvarchar2类型与-nationalCharacterSet AL16UTF16有关.换一个参数看看.另外写一篇blog.