oracle字符集迁移,oracle数据库字符集characterset迁移及变更系列二

背景闲言少述，继续测试数据库字符集相关的知识，前几文链接如下：oracle数据库字符集characterset迁移及变更系列一http://blog.itpub.net/9240380/viewspace-1849953/本文主要熟悉下数据库字符集迁移要考虑一些因素。结论1,zhs16gbk及al32utf8，消耗的空间是相同的，但二者采用编码是不同的2,如果源字符集与目标字符集不相同，导入时会根

高工-老罗

1381人浏览 · 2021-04-03 03:11:54

高工-老罗 · 2021-04-03 03:11:54 发布

背景

闲言少述，继续测试数据库字符集相关的知识，前几文链接如下：

oracle数据库字符集characterset迁移及变更系列一

http://blog.itpub.net/9240380/viewspace-1849953/

本文主要熟悉下数据库字符集迁移要考虑一些因素。

结论

1,zhs16gbk及al32utf8，消耗的空间是相同的，但二者采用编码是不同的

2,如果源字符集与目标字符集不相同，导入时会根据目标数据库字符集把数据进行重组然后存储

3,基于char固定宽度的字符数据，如果从zhs16gbk到al32utf8,会提示如下错误信息：

Column 1 我们

IMP-00019: row rejected due to ORACLE error 12899

IMP-00003: ORACLE error 12899 encountered

ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)

为了解决上述问题，有2种方法：

A,根据报错增加迁移表的列长度，然后重新IMP数据即可

B,配置参数blank_trimming=true，不过要重启库方可生效，即移除列尾部的空格字符

4，与CHAR不同，同样是上述的测试环境，如果是VARCHAR2，则可以迁移成功，可见VARCHAR2的优点明显高于CHAR，且消耗空间小，性能更高

所以在设计数据库时要考虑到这些因素

5，同样是基于上述的测试环境，可以把字符数据定义为clob,就可以避免VARCHAR或CHAR类型引发的数据损失或丢失或破坏

6，数据库用户及密码只能是ASCII字符，不能是非ASCII字符，否则数据会无法迁移，且会报错退出

IMP-00058: ORACLE error 1017 encountered

ORA-01017: invalid username/password; logon deniedUsername:

Password:

不过可以基于源数据库的用户，在目标库构建对应权限的新用户，然后IMP FROMUSER TOUSER即可

当然最高效的方法就是在迁移数据时，确保源数据库的用户密码只能包括ASCII字符

分析思路

测试

---为了测试不同数据库字符集之间的变更及迁移的原理，DBCA创建了不同数据库字符集的数据库实例

[oracle@seconary admin]$ ll

total 24

drwxr-x--- 5 oracle oinstall 4096 Nov 29 18:58 charzhs --ZHS16GBK

drwxr-x--- 5 oracle oinstall 4096 Apr 17 2014 guowang --WE8MSWIN1252

drwxr-x--- 5 oracle oinstall 4096 Sep 11 00:44 newb

drwxr-x--- 5 oracle oinstall 4096 Nov 28 16:45 onetime

drwxr-xr-x 3 oracle oinstall 4096 Aug 28 2013 second

drwxr-x--- 5 oracle oinstall 4096 Nov 29 18:35 utf8char -- AL32UTF8

--先看下不同数据库字符集的空间占用差异

---al32utf8

[oracle@seconary admin]$ export ORACLE_SID=utf8char

SQL> conn /as sysdba

Connected.

SQL> alter user scott identified by system account unlock;

User altered.

SQL> conn scott/system

Connected.

SQL> create table t_charset(a char(10));

Table created.

SQL> insert into t_charset values('我们');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t_charset select * from t_charset;

1 row created.

SQL> insert into t_charset select * from t_charset;

2 rows created.

中间略

SQL> insert into t_charset select * from t_charset;

262144 rows created.

SQL> commit;

Commit complete.

SQL>

SQL> select count(*) from t_charset;

COUNT(*)

----------

524288

SQL> select segment_name,bytes from user_segments where lower(segment_name)='t_charset';

SEGMENT_NAME BYTES

-------------------------------------------------- ----------

T_CHARSET 10485760

SQL> select a,dump(a) from t_charset where rownum<=10;

A DUMP(A)

---------- ----------------------------------------------------------------------------------------------------

我们 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32

10 rows selected.

--zhs16gbk

SQL> conn scott/system

Connected.

SQL> create table t_charset(a char(10));

Table created.

SQL> insert into t_charset values('我们');

1 row created.

SQL> commit;

SQL> select count(*) from t_charset;

COUNT(*)

----------

524288

可见zhs16gbk及al32utf8基于中文字符消耗的空间是相同的

SQL> select segment_name,bytes from user_segments where lower(segment_name)='t_charset';

SEGMENT_NAME BYTES

--------------------------------------------------------------------------------- ----------

T_CHARSET 10485760

不过2种数据库字符集采用虽然都是10个字节，但底层采用的内部编码不一样

SQL> select a,dump(a) from t_charset where rownum<=10;

A DUMP(A)

------------------------------ ----------------------------------------------------------------------------------------------------

我们 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32

10 rows selected.

---测试下基于2种数据库字符集不同，EXP以及IMP的情况,al32utf8到zhs16gbk

---导出AL32UTF8

[oracle@seconary admin]$ export ORACLE_SID=utf8char

[oracle@seconary admin]$ env|grep NLS_LANG

NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

[oracle@seconary admin]$ exp userid=scott/system file=exp_utf8char.dmp tables=t_charset

Export: Release 11.2.0.1.0 - Production on Sun Nov 29 21:53:12 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table T_CHARSET 524288 rows exported

Export terminated successfully without warnings.

[oracle@seconary admin]$

---备份下zhs16gbk的测试表，然后删除测试表

[oracle@seconary admin]$ export ORACLE_SID=charzhs

[oracle@seconary admin]$ sqlplus scott/system

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 29 21:55:29 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t_charset_bak as select * from t_charset;

Table created.

SQL> drop table t_charset purge;

Table dropped.

可见导入目标数据库字符集后，会基于目标数据库字符集重新组织源数据库字符集对应的字符数据

[oracle@seconary admin]$ imp userid=scott/system file=exp_utf8char.dmp tables=t_charset

Import: Release 11.2.0.1.0 - Production on Sun Nov 29 21:57:10 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SCOTT's objects into SCOTT

. . importing table "T_CHARSET" 524288 rows imported

Import terminated successfully without warnings.

SQL> select a,dump(a) from t_charset where rownum<=10;

A DUMP(A)

------------------------- --------------------------------------------------------------------------------

我们 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32

10 rows selected.

---测试下由zhs16gbk到al32utf8

[oracle@seconary admin]$ env|grep SID

ORACLE_SID=charzhs

[oracle@seconary admin]$ exp userid=scott/system file=exp_charzhs.dmp tables=t_charset_bak

Export: Release 11.2.0.1.0 - Production on Sun Nov 29 22:00:57 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table T_CHARSET_BAK 524288 rows exported

Export terminated successfully without warnings.

[oracle@seconary admin]$ imp userid=scott/system file=exp_charzhs.dmp tables=t_charset_bak

ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)

Column 1 我们

IMP-00019: row rejected due to ORACLE error 12899

IMP-00003: ORACLE error 12899 encountered

ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)

Column 1 我们

IMP-00019: row rejected due to ORACLE error 12899

IMP-00003: ORACLE error 12899 encountered

ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)

Column 1 我们

IMP-00019: row rejected due to ORACLE error 12899

IMP-00003: ORACLE error 12899 encountered

ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)

Column 1 我们

IMP-00019: row rejected due to ORACLE error 12899

IMP-00003: ORACLE error 12899 encountered

ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)

Column 1 我们

IMP-00019: row rejected due to ORACLE error 12899

IMP-00003: ORACLE error 12899 encountered

ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)

--调整表的列长度为13字节

SQL> desc t_charset_bak;

Name Null? Type

----------------------------------------- -------- ----------------------------

A CHAR(10)

SQL> alter table t_charset_bak modify a char(13);

Table altered.

--调整列长度后导入成功

[oracle@seconary admin]$ imp userid=scott/system file=exp_charzhs.dmp tables=t_charset_bak ignore=y

Import: Release 11.2.0.1.0 - Production on Sun Nov 29 22:43:15 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing SCOTT's objects into SCOTT

. . importing table "T_CHARSET_BAK" 524288 rows imported

Import terminated successfully without warnings.

[oracle@seconary admin]$

SQL> col dump(a) for a80

SQL> select a,dump(a) from t_charset_bak where rownum<=10;

A DUMP(A)

-------------------------- --------------------------------------------------------------------------------

我们 Typ=96 Len=13: 233,142,180,230,136,156,230,187,145,32,32,32,32

10 rows selected.

SQL> select a,trim(a),dump(trim(a)) from t_charset_bak where rownum<=10;

A TRIM(A) DUMP(TRIM(A))

-------------------------- -------------------------- ------------------------------------------------------------

我们我们 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145

10 rows selected.

---官方说通过配置如下参数blank_trimming=TRUE可以，经测确实可以导入ZHS1GGBK到AL32UTF8的CHAR数据

SQL> show parameter trim

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

blank_trimming boolean FALSE

SQL>

SQL> conn /as sysdba

Connected.

SQL> alter system set blank_trimming=true;

alter system set blank_trimming=true

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set blank_trimming=true scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size 2213896 bytes

Variable Size 956303352 bytes

Database Buffers 687865856 bytes

Redo Buffers 7135232 bytes

Database mounted.

Database opened.

SQL> show parameter trim

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

blank_trimming boolean TRUE

SQL>

SQL> conn scott/system

Connected.

SQL> drop table t_charset_bak purge;

Table dropped.

[oracle@seconary admin]$ env|grep SID

ORACLE_SID=utf8char

[oracle@seconary admin]$ imp userid=scott/system file=exp_charzhs.dmp tables=t_charset_bak

Import: Release 11.2.0.1.0 - Production on Sun Nov 29 22:56:02 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing SCOTT's objects into SCOTT

. . importing table "T_CHARSET_BAK" 524288 rows imported

Import terminated successfully without warnings.

SQL> select a,trim(a),dump(trim(a)) from t_charset_bak where rownum<=10;

A TRIM(A) DUMP(TRIM(A))

-------------------- ------------------------------ --------------------------------------------------------------------------------

我们我们 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145

10 rows selected.

---测试下基于varchar2类型，由zhs16gbk到al32utf8,可见varchar就比char更好用，在数据迁移中，它会更好，且消耗空间少，所以在设计表结构时，一定要进行仔细考虑

---zhs16gbk

SQL> create table t_varchar(a varchar2(10));

Table created.

SQL> insert into t_charset values('我们');

1 row created.

SQL> commit;

Commit complete.

[oracle@seconary admin]$ exp userid=scott/system file=exp_charzhs_varchar.dmp tables=t_varchar

Export: Release 11.2.0.1.0 - Production on Sun Nov 29 23:12:39 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table T_VARCHAR 1 rows exported

Export terminated successfully without warnings.

[oracle@seconary admin]$ export ORACLE_SID=utf8char

[oracle@seconary admin]$ imp userid=scott/system file=exp_charzhs_varchar.dmp tables=t_varchar

Import: Release 11.2.0.1.0 - Production on Sun Nov 29 23:13:21 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing SCOTT's objects into SCOTT

. . importing table "T_VARCHAR" 1 rows imported

Import terminated successfully without warnings.

[oracle@seconary admin]$

SQL> select a,trim(a),dump(trim(a)) from t_charset_bak where rownum<=10;

A TRIM(A) DUMP(TRIM(A))

-------------------- ------------------------------ --------------------------------------------------------------------------------

我们我们 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145

10 rows selected.

---测试下zhs16gbk到al32utf8的CLOB列的迁移,可见把字符类型的数据，可以定义为clob,就可以避免VARCHAR或CHAR类型引发的数据损失或丢失或破坏

SQL> create table t_clob(a clob);

Table created.

SQL> insert into t_clob values('我们');

1 row created.

SQL> commit;

Commit complete.

SQL> select a,dump(a) from t_clob;

select a,dump(a) from t_clob

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected - got CLOB

SQL> select a from t_clob;

--------------------------------------------------------------------------------

我们

ORACLE_SID=charzhs

[oracle@seconary admin]$ exp userid=scott/system file=exp_charzhs_clob.dmp tables=t_clob

Export: Release 11.2.0.1.0 - Production on Mon Nov 30 00:32:05 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table T_CLOB 1 rows exported

Export terminated successfully without warnings.

[oracle@seconary admin]$

[oracle@seconary admin]$ export ORACLE_SID=utf8char

[oracle@seconary admin]$ imp userid=scott/system file=exp_charzhs_clob.dmp tables=t_clob

Import: Release 11.2.0.1.0 - Production on Mon Nov 30 00:32:36 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing SCOTT's objects into SCOTT

. . importing table "T_CLOB" 1 rows imported

Import terminated successfully without warnings.

SQL> select a,dump(a) from t_clob;

select a,dump(a) from t_clob

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected - got CLOB

SQL> select a from t_clob;

--------------------------------------------------------------------------------

我们

---测试下用户密码为非ASCII字符，基于ZHS16GBK到AL32UTF8

----可见用户密码不能包括中文字符

[oracle@seconary admin]$ env|grep SID

ORACLE_SID=charzhs

SQL> alter user scott identified by '1翟勋杨1' account unlock;

alter user scott identified by '1翟勋杨1' account unlock

ERROR at line 1:

ORA-00988: missing or invalid password(s)

SQL> desc user$;

Name Null? Type

----------------------------------------- -------- ----------------------------

USER# NOT NULL NUMBER

NAME NOT NULL VARCHAR2(30)

TYPE# NOT NULL NUMBER

PASSWORD VARCHAR2(30)

SQL> create user zxy翟z identified by system account unlock;

User created.

SQL> select username from dba_users where lower(username)='zxy翟z';

USERNAME

------------------------------

ZXY翟z

SQL> grant resource,connect to zxy翟z;

Grant succeeded.

SQL> conn zxy翟z/system

Connected.

SQL> create table t_testv(a int);

Table created.

SQL> insert into t_testv values(1);

1 row created.

SQL> commit;

Commit complete.

[oracle@seconary admin]$ exp userid=zxy翟z/system file=exp_user1.dmp

Export: Release 11.2.0.1.0 - Production on Mon Nov 30 00:56:04 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user ZXY翟z

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user ZXY翟z

About to export ZXY翟z's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export ZXY翟z's tables via Conventional Path ...

. . exporting table T_TESTV 1 rows exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

[oracle@seconary admin]$

--可见确实用户密码只能是ASCII，不能包括特殊字符，比如中文，否则会报错，无法迁移数据，只能采用重建对应的用户，然后迁移到这个新用户

[oracle@seconary admin]$ export ORACLE_SID=utf8char

[oracle@seconary admin]$ imp userid=zxy翟z/system file=exp_user1.dmp

Import: Release 11.2.0.1.0 - Production on Mon Nov 30 00:57:14 2015

IMP-00058: ORACLE error 1017 encountered

ORA-01017: invalid username/password; logon deniedUsername:

Password:

ORA-01017: invalid username/password; logon denied

IMP-00005: all allowable logon attempts failed

IMP-00000: Import terminated unsuccessfully

SQL> create user ch_china identified by system account unlock;

User created.

SQL> grant resource,connect to ch_china;

Grant succeeded.

[oracle@seconary admin]$ imp userid=system/system fromuser=zxy翟z touser=ch_china file=exp_user1.dmp

Import: Release 11.2.0.1.0 - Production on Mon Nov 30 01:00:43 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by ZXY翟z, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing ZXY翟z's objects into CH_CHINA

. . importing table "T_TESTV" 1 rows imported

Import terminated successfully without warnings.

[oracle@seconary admin]$ sqlplus ch_china/system

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 01:01:04 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from t_testv;

----------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1850079/，如需转载，请注明出处，否则将追究法律责任。

DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐

COSER: Coordinating LLM-Based Persona Simulation of Established Roles

DAMO开发者矩阵

抗磁、抗窄、抗微动！Captiks全身惯性动捕系统攻克车内精细动作捕捉难题

DAMO开发者矩阵

EM-Core-Agent：AI Agent 具身认知核心系统——架构白皮书 V1.0

DAMO开发者矩阵

所有评论(0)

查看更多评论

高工-老罗

@weixin_35762250

已为社区贡献2条内容

oracle字符集迁移,oracle数据库字符集characterset迁移及变更系列二

高工-老罗

所有评论(0)

温馨提示：您尚未绑定手机号

高工-老罗