[20260628]测试不同字符集使用dblink的情况.txt

[20260628]测试不同字符集使用dblink的情况.txt

--//测试不同字符集使用dblink的情况,数据库oracle 21c。
--//网络地址       主机名   数据库字符集        国家字符集
--//192.168.56.101 centtest 使用ZHS16GBK字符集  AL16UTF16
--//192.168.56.103 centos9  使用AL32UTF8字符集  AL16UTF16

1.分别在两台机器建立db_link连接:
--//192.168.56.103 centos9
CREATE PUBLIC DATABASE LINK centtest
 CONNECT TO SCOTT
 IDENTIFIED BY book
 USING '192.168.56.101/book01p';

--//192.168.56.101 centtest
CREATE PUBLIC DATABASE LINK centos9
 CONNECT TO SCOTT
 IDENTIFIED BY book
 USING '192.168.56.103/book01p';

2.两边同时建立表:

--//create table t1 (id number ,vc varchar2(20),nvc nvarchar2(40));

--//192.168.56.101 centtest
SCOTT@book01p> insert into t1 values (1,'1测试centtest2','a测试centtestb');
1 row created.

SCOTT@book01p> commit;
Commit complete.

--//192.168.56.103 centos9
SCOTT@book01p> insert into t1 values (2,'3测试centos9_4','c测试centos9_d');
1 row created.

SCOTT@book01p> commit ;
Commit complete.

SCOTT@book01p> select 'centos9' txt ,t1.* from t1 union all select 'centtest',t2.* from t1@centtest t2;
TXT              ID VC              NVC
-------- ---------- --------------- ----------------
centos9           2 3测试centos9_4  c测试centos9_d
centtest          1 1测试centtest2  a测试centtestb

--//192.168.56.101 centtest
SCOTT@book01p> select 'centtest' txt ,t1.* from t1 union all select 'centos9',t2.* from t1@centos9 t2;
TXT              ID VC              NVC
-------- ---------- --------------- ----------------
centtest          1 1测试centtest2  a测试centtestb
centos9           2 3测试centos9_4  c测试centos9_d
--//两边显示中文没有问题。

--//192.168.56.101 centtest
SCOTT@book01p> select 'centtest' txt ,t1.id,t1.vc,dump(t1.vc,16) c60 from t1 union all select 'centos9',t2.id,t2.vc,dump(t2.vc,16) c60 from t1@centos9 t2;
TXT              ID VC              C60
-------- ---------- --------------- ------------------------------------------------------------
centtest          1 1测试centtest2  Typ=1 Len=14: 31,b2,e2,ca,d4,63,65,6e,74,74,65,73,74,32
centos9           2 3测试centos9_4  Typ=1 Len=14: 33,b2,e2,ca,d4,63,65,6e,74,6f,73,39,5f,34
--//b2,e2,ca,d4 = 测试,可以发现显示时自动转换为ZHS16GBK的编码。

--//192.168.56.103 centos9
SCOTT@book01p> select 'centos9' txt ,t1.id,t1.vc,dump(t1.vc,16) c70 from t1 union all select 'centtest',t2.id,t2.vc,dump(t2.vc,16) c70 from t1@centtest t2;
TXT              ID VC               C70
-------- ---------- ---------------- ----------------------------------------------------------------------
centos9           2 3测试centos9_4   Typ=1 Len=16: 33,e6,b5,8b,e8,af,95,63,65,6e,74,6f,73,39,5f,34
centtest          1 1测试centtest2   Typ=1 Len=16: 31,e6,b5,8b,e8,af,95,63,65,6e,74,74,65,73,74,32

--//63,65,6e,74,6f,73,39,5f,34 = centos9_4
--//e6,b5,8b,e8,af,95 = 测试,可以发现显示时自动转换为utf-8编码。
$ echo "e6,b5,8b,e8,af,95" | tr -d ',' | xxd -r -p ; echo
测试
--//也可以确定通过dblink使用dump看到并非数据块保存的编码。

--//192.168.56.103 centos9,长度问题，utf-8汉字编码占用3个字节，字段长度20也就是最多6个汉字。
--//192.168.56.103 centos9
SCOTT@book01p> insert into t1 values (3,'字符集字符集字符集字','aaaa');
insert into t1 values (3,'字符集字符集字符集字','aaaa')
                         *
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T1"."VC" (actual: 30, maximum: 20)

SCOTT@book01p> insert into t1 values (3,'字符集字符集123','aaaa');
insert into t1 values (3,'字符集字符集123','aaaa')
                         *
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T1"."VC" (actual: 21, maximum: 20)

SCOTT@book01p> insert into t1 values (3,'字符集字符集12','aaaa');
1 row created.

--//192.168.56.101 centtest
SCOTT@book01p> insert into t1 values (3,'字符集字符集字符集字','aaaa');
1 row created.

SCOTT@book01p> commit;
Commit complete.

--//可以发现在zhs16gbk字符集下插入10个汉字就没有问题。换一句话如果在AL32UTF8字符集下，varchar2输入汉字必须考虑的最大长度
--//是zhs16gbk字符集的字段长度乘以1.5.

--//另外注意一个细节，定义表varchar2可以指定单位，缺省不指定是byte，可以定义为char，例子如下：
--//192.168.56.103 centos9
COTT@book01p> create table t2 (id number ,vc varchar2(20 char),nvc nvarchar2(40));
Table created.

SCOTT@book01p> @ desc t2
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ID                                       NUMBER
    2      VC                                       VARCHAR2(20 CHAR)
    3      NVC                                      NVARCHAR2(40)

SCOTT@book01p> @ desc t1
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ID                                       NUMBER
    2      VC                                       VARCHAR2(20)
    3      NVC                                      NVARCHAR2(40)
--//注意细节。

COTT@book01p> insert into t2 values (3,'字符集字符集字符集字','aaaa');
1 row created.

SCOTT@book01p> insert into t2 values (4,'字符集字符集字符集字字符集字符集字符集字','aaaa');
1 row created.
--//插入20个字符没有问题。

SCOTT@book01p> insert into t2 values (4,'字符集字符集字符集字字符集字符集字符集字1','aaaa');
insert into t2 values (4,'字符集字符集字符集字字符集字符集字符集字1','aaaa')
                         *
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T2"."VC" (actual: 21, maximum: 20)
--//插入21个字符保存。

SCOTT@book01p> insert into t2 values (5,'1234567890abcdefghij','aaaa');
1 row created.

SCOTT@book01p> insert into t2 values (5,'1234567890abcdefghij1','aaaa');
insert into t2 values (5,'1234567890abcdefghij1','aaaa')
                         *
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T2"."VC" (actual: 21, maximum: 20)

--//可以发现存在奇怪现象，插入"字符集字符集字符集字字符集字符集字符集字"可以,而插入'1234567890abcdefghij1'报错的奇怪现象.