[20221012]簡單探究nvarchar2資料型別儲存.txt

[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.