数据库不同字符集迁移
一．引言
ORACLE
数据库字符集，即Oracle全球化支持(Globalization Support)，或即国家语言支持（NLS）其作用是用本国语言和格式来存储、处理和检索数据。利用全球化支持，ORACLE为用户提供自己熟悉的数据库母语环境，诸如日期格式、数字格式和存储序列等。Oracle可以支持多种语言及字符集，其中oracle8i支持48种语言、76个国家地域、229种字符集，而oracle9i则支持57种语言、88个国家地域、235种字符集。由于oracle字符集种类多，且在存储、检索、迁移oracle数据时多个环节与字符集的设置密切相关，因此在实际的应用中，数据库开发和管理人员经常会遇到有关oracle字符集方面的问题。本文通过以下几个方面阐述，对oracle字符集做简要分析
二．字符集基本知识
2.1
字符集实质就是按照一定的字符编码方案，对一组特定的符号，分别赋予不同数值编码的集合。Oracle数据库最早支持的编码方案是US7ASCII。
Oracle
的字符集命名遵循以下命名规则:
即: <语言><比特位数><编码>
比如: ZHS16GBK表示采用GBK编码格式、16位（两个字节）简体中文字符集
2.2
字符编码方案
2.2.1
单字节编码（1）单字节7位字符集，可以定义128个字符，最常用的字符集为US7ASCII
（2）单字节8位字符集，可以定义256个字符，适合于欧洲大部分国家例如：WE8ISO8859P1(西欧、8位、ISO标准8859P1编码)
2.2.2
多字节编码（1）变长多字节编码某些字符用一个字节表示，其它字符用两个或多个字符表示，变长多字节编码常用于对亚洲语言的支持， 例如日语、汉语、印地语等例如：AL32UTF8（其中AL代表ALL,指适用于所有语言）、zhs16cgb231280
（2）定长多字节编码每一个字符都使用固定长度字节的编码方案，目前oracle唯一支持的定长多字节编码是AF16UTF16，也是仅用于国家字符集
2.2.3 unicode
编码
Unicode
是一个涵盖了目前全世界使用的所有已知字符的单一编码方案，也就是说Unicode为每一个字符提供唯一的编码。UTF-16是unicode的16位编码方式，是一种定长多字节编码，用2个字节表示一个unicode字符，AF16UTF16是UTF-16编码字符集。
UTF-8
是unicode的8位编码方式，是一种变长多字节编码，这种编码可以用1、2、3个字节表示一个unicode字符，AL32UTF8，UTF8、UTFE是UTF-8编码字符集
2.4
数据库字符集（oracle服务器端字符集）数据库字符集在创建数据库时指定，在创建后通常不能更改。在创建数据库时，可以指定字符集(CHARACTER SET)和国家字符集(NATIONAL CHARACTER
SET)。
2.5
客户端字符集（NLS_LANG参数）
2.5.1
客户端字符集含义客户端字符集定义了客户端字符数据的编码方式，任何发自或发往客户端的字符数据均使用客户端定义的字符集编码,客户端可以看作是能与数据库直接连接的各种应用，例如sqlplus,exp/imp等。客户端字符集是通过设置NLS_LANG参数来设定的。
2.5.2 NLS_LANG
参数格式
NLS_LANG=_.
Language:
显示oracle消息,校验，日期命名
Territory
：指定默认日期、数字、货币等格式
Client character set
：指定客户端将使用的字符集例如：NLS_LANG=AMERICAN_AMERICA.US7ASCII
AMERICAN
是语言，AMERICA是地区，US7ASCII是客户端字符集
三．导入/导出与字符集转换
3.1 EXP/IMP
Export
和 Import 是一对读写Oracle数据的工具。Export 将Oracle 数据库中的数据输出到操作系统文件中, Import 把这些文件中的数据读到Oracle 数据库中，由于使用exp/imp进行数据迁移时，数据从源数据库到目标数据库的过程中有四个环节涉及到字符集，如果这四个环节的字符集不一致，将会发生字符集转换。
四个字符集是（1）源数据库字符集（2）Export过程中用户会话字符集（通过NLS_LANG设定）（3）Import过程中用户会话字符集（通过NLS_LANG设定）（4）目标数据库字符集
3.2
导出的转换过程在Export过程中，如果源数据库字符集与Export用户会话字符集不一致，会发生字符集转换，并在导出文件的头部几个字节中存储Export用户会话字符集的ID号。在这个转换过程中可能发生数据的丢失。例:如果源数据库使用ZHS16GBK，而Export用户会话字符集使用US7ASCII，由于ZHS16GBK是16位字符集,而US7ASCII是7位字符集，这个转换过程中，中文字符在US7ASCII中不能够找到对等的字符，所以所有中文字符都会丢失而变成“?? ”形式，这样转换后生成的Dmp文件已经发生了数据丢失。因此如果想正确导出源数据库数据，则Export过程中用户会话字符集应等于源数据库字符集或是源数据库字符集的超集
3.3
导入的转换过程（1）确定导出数据库字符集环境通过读取导出文件头，可以获得导出文件的字符集设置（2）确定导入session的字符集，即导入Session使用的NLS_LANG环境变量（3）IMP读取导出文件读取导出文件字符集ID，和导入进程的NLS_LANG进行比较（4）如果导出文件字符集和导入Session字符集相同，那么在这一步骤内就不需要转换，如果不同，就需要把数据转换为导入Session使用的字符集。可以看出，导入数据到数据库过程中发生两次字符集转换第一次:导入文件字符集与导入Session使用的字符集之间的转换，如果这个转换过程不能正确完成，Import向目标数据库的导入过程也就不能完成。第二次:导入Session字符集与数据库字符集之间的转换。然而,oracle8i的这种转换只能在单字节字符集之间进行,oracle8i导入Session不支持多字节字符集之间的转换，因此为了避免第一次转换，导入Session使用的NLS_LANG与导出文件字符集相同，第二次转换（通过SQL*Net）支持任何两种字符集。以上情况在Oracle9i中略有不同
Server
1
SQL> SELECT * FROMNls_database_parameters WHERE Parameter in ('NLS_CHARACTERSET
PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
ZHS16GBK
NLS_NCHAR_CHARACTERSET
AL16UTF16
Server
2
SQL> SELECT * FROMNls_database_parameters WHERE Parameter in ('NLS_CHARACTERSET
','NLS_NCHAR_CHARACTERSET');
PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8
NLS_NCHAR_CHARACTERSET
AL16UTF16
Server
1,2
SQL> create user wzx_test identified byoracle;
用户已创建。
SQL> grant dba to wzx_test;
授权成功。
Server
1
Connect wzx_test/oracle
Create table t(id int,name varchar2(20));
Insert into t values(1,'振兴中华振兴中华振兴') ;
Insert into t values(1,'振兴中华') ;
C:\Users\Administrator>setNLS_LANG=american_america.AL32UTF8
C:\Users\Administrator>expdp
wzx_test/oracle schemas=wzx_test directory=DATA_PUMP_DIR dumpfile=wzx_20170912
logfile=wzx_expdp_20170912
Export:
Release 11.2.0.3.0 - Production on Tue Sep 12 13:30:35 2017
Copyright(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected
to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
FLASHBACK
automatically enabled to preserve database integrity.
Starting"WZX_TEST"."SYS_EXPORT_SCHEMA_01": wzx_test/******** schemas=wzx_test directory=
12
Estimate
in progress using BLOCKS method...
Processing
object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total
estimation using BLOCKS method: 64 KB
Processing
object type SCHEMA_EXPORT/USER
Processing
object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing
object type SCHEMA_EXPORT/ROLE_GRANT
Processing
object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing
object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing
object type SCHEMA_EXPORT/TABLE/TABLE
Processing
object type SCHEMA_EXPORT/TABLE/COMMENT
Processing
object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing
object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing
object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. .exported "WZX_TEST"."T" 5.453 KB 2 rows
Master
table "WZX_TEST"."SYS_EXPORT_SCHEMA_01" successfully
loaded/unloaded
******************************************************************************
Dump
file set for WZX_TEST.SYS_EXPORT_SCHEMA_01 is:
D:\APP\ADMINISTRATOR\ADMIN\SFISMAO\DPDUMP\WZX_20170912.DMP
Job
"WZX_TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at
13:30:49
Server
2
C:\Users\Administrator>setNLS_LANG=american_america.AL32UTF8
C:\Users\Administrator>impdpwzx_test/oracle schemas=wzx_test directory=DATA_PUM
P_DIR dumpfile=wzx_20170912logfile=wzx_impdp_20170912
Import:
Release 11.2.0.4.0 - Production on Tue Sep 12 13:34:30 2017
Copyright(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected
to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Master
table "WZX_TEST"."SYS_IMPORT_SCHEMA_01" successfully
loaded/unloaded
Starting"WZX_TEST"."SYS_IMPORT_SCHEMA_01": wzx_test/******** schemas=wzx_test
directory=DATA_PUMP_DIR
dumpfile=wzx_20170912 logfile=wzx_impdp_20170912
Processing
object type SCHEMA_EXPORT/USER
ORA-31684:
Object type USER:"WZX_TEST" already exists
Processing
object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing
object type SCHEMA_EXPORT/ROLE_GRANT
Processing
object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing
object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing
object type SCHEMA_EXPORT/TABLE/TABLE
Processing
object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-02374: conversion errorloading table "WZX_TEST"."T"
ORA-12899: value too large forcolumn NAME (actual: 30, maximum: 20)
ORA-02372: data for row: NAME :0X'D5F1D0CBD6D0BBAAD5F1D0CBD6D0BBAAD5F1D0CB'
. .imported "WZX_TEST"."T" 5.453 KB 1 out of
2 rows
Job
"WZX_TEST"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s)
at Tue Sep 12 13
:34:33
2017 elapsed 0 00:00:02
Server
1
C:\Users\Administrator>sqlpluswzx_test/oracle@172.20.1.170:1521/wzxtest
SQL*Plus:
Release 11.2.0.3.0 Production on Tue Sep 12 13:41:34 2017
Copyright(c) 1982, 2011, Oracle. All rights reserved.
Connected
to:
Oracle
Database 11g Release 11.2.0.4.0 - 64bit Production
SQL>
select * from t;
ID NAME
----------
----------------------------------------
1振兴中华
为了避免在数据库迁移过程中由于字符集不同导致的数据损失，oracle提供了字符集扫描工具（character set scanner），通过这个工具我们可以测试在数据迁移过程中由于字符集转换可能带来的问题，然后根据测试结果，确定数据迁移过程中最佳字符集解决方案。
