Oracle中IMP导入数据时提示字符集不一致解决

154次阅读

共计 12900 个字符，预计需要花费 33 分钟才能阅读完成。

生产环境中经常使用到 Oracle 的 IMP 导入和 EXP 导出来功能来达到数据迁移的目的, 通常在源数据库和目标数据库中查询字符集是否致,

测试环境中导入 IMP 导入报错信息如下:

导入命令如下:

[oracle@localhost.localdomain:/soft]$imp scott/scott file=/soft/scott_v1.dmp log=/soft/scott_v1.log ignore=y full=y

Import: Release 11.2.0.4.0 – Production on Sun Oct 22 16:26:27 2017

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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 “BONUS” 0 rows imported
. . importing table “DEPT”
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated– 提示各种乱码问题
Column 1 10
Column 2 ǎ
Column 3
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
Column 1 20
Column 2 в
Column 3 ±±
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
Column 1 30
Column 2 к
Column 3
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
Column 1 40
Column 2
Column 3 0 rows imported
. . importing table “EMP”
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7369
Column 2 itpux01
Column 3 τ
Column 4 7902
Column 5 17-DEC-1980:00:00:00
Column 6 800
Column 7
Column 8 20
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7499
Column 2 itpux02
Column 3 к
Column 4 7698
Column 5 20-FEB-1981:00:00:00
Column 6 1600
Column 7 300
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7521
Column 2 itpux03
Column 3 к
Column 4 7698
Column 5 22-FEB-1981:00:00:00
Column 6 1250
Column 7 500
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7566
Column 2 itpux04
Column 3
Column 4 7839
Column 5 02-APR-1981:00:00:00
Column 6 2975
Column 7
Column 8 20
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7654
Column 2 itpux05
Column 3 к
Column 4 7698
Column 5 28-SEP-1981:00:00:00
Column 6 1250
Column 7 1400
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7698
Column 2 itpux06
Column 3
Column 4 7839
Column 5 01-MAY-1981:00:00:00
Column 6 2850
Column 7
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7782
Column 2 itpux07
Column 3
Column 4 7839
Column 5 09-JUN-1981:00:00:00
Column 6 2450
Column 7
Column 8 10
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7788
Column 2 itpux08
Column 3 ·
Column 4 7566
Column 5 19-APR-1987:00:00:00
Column 6 3000
Column 7
Column 8 20
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7839
Column 2 itpux09
Column 3
Column 4
Column 5 17-NOV-1981:00:00:00
Column 6 5000
Column 7
Column 8 10
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7844
Column 2 itpux10
Column 3 к
Column 4 7698
Column 5 08-SEP-1981:00:00:00
Column 6 1500
Column 7 0
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7876
Column 2 itpux11
Column 3 τ
Column 4 7788
Column 5 23-MAY-1987:00:00:00
Column 6 1100
Column 7
Column 8 20
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7900
Column 2 itpux12
Column 3 τ
Column 4 7698
Column 5 03-DEC-1981:00:00:00
Column 6 950
Column 7
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7902
Column 2 itpux13
Column 3 ·
Column 4 7566
Column 5 03-DEC-1981:00:00:00
Column 6 3000
Column 7
Column 8 20
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7934
Column 2 itpux14
Column 3 τ
Column 4 7782
Column 5 23-JAN-1982:00:00:00
Column 6 1300
Column 7
Column 8 10 0 rows imported
. . importing table “SALGRADE” 5 rows imported
About to enable constraints…
Import terminated successfully with warnings.

如何查询字符集

方法 1: 查询表(在知道源数据库和目标数据库):

select * from nls_database_parameters;

1 NLS_LANGUAGE AMERICAN
2 NLS_TERRITORY AMERICA
3 NLS_CURRENCY $
4 NLS_ISO_CURRENCY AMERICA
5 NLS_NUMERIC_CHARACTERS .,
6 NLS_CHARACTERSET ZHS16GBK – 此处是显示字符集
7 NLS_CALENDAR GREGORIAN
8 NLS_DATE_FORMAT DD-MON-RR
9 NLS_DATE_LANGUAGE AMERICAN
10 NLS_SORT BINARY
11 NLS_TIME_FORMAT HH.MI.SSXFF AM
12 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
13 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
14 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
15 NLS_DUAL_CURRENCY $
16 NLS_COMP BINARY
17 NLS_LENGTH_SEMANTICS BYTE
18 NLS_NCHAR_CONV_EXCP FALSE
19 NLS_NCHAR_CHARACTERSET AL16UTF16
20 NLS_RDBMS_VERSION 11.2.0.4.0

方法 2: 在不知道 dmp 中字符集是什么, 如何查看

1 查看 dmp 文件的中字符集

cat ‘/home/soft/scott.dmp‘ |od -x|head -1|awk ‘{print $2 $3}’|cut -c 3-6

结果为：0345

2 然后用以下 SQL 查出它对应的字符集:

SQL> select nls_charset_name(to_number(‘0354′,’xxxx’)) from dual;
NLS_CHARSET_NAME(TO_NUMBER(‘0354′,’XXXX’
—————————————-
ZHS16GBK

如何修改字符集

修改字符集有几种方法

方法 1、修改服务器端中的环境变量中的字符集设置（服务器端导入只要配置服务器端，客户端导入的话还要设置客户端字符集），注：此方法在服务器修改后再及时修改回去

方法 2、修改数据库中字符集

方法 3、修改 dmp 文件中字符集

本环境是在服务器端导入的，所以采用方法 1

1、查询当前环境变量

[oracle@localhost.localdomain:/soft]$cat ~/.bash_profile
# .bash_profile

# Get the aliases and functions
if [-f ~/.bashrc]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

# Oracle Settings oracle
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
PS1=”[`whoami`@`hostname`:”‘$PWD]$’
alias sqlplus=”rlwrap sqlplus”
alias rman=”rlwrap rman”
alias alert=”cd /oracle/app/oracle/diag/rdbms/db01/db01/trace”
alias lsnrctl=”rlwrap lsnrctl”
alias adrci=”rlwrap adrci”
export LANG=en_US
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0; export ORACLE_HOME
ORACLE_SID=db01; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
NLS_DATE_FORMAT=”yyyy-mm-dd HH24:MI:SS”; export NLS_DATE_FORMAT
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK;export NLS_LANG

if [$USER = “oracle”] || [$USER = “grid”]; then
if [$SHELL = “/bin/ksh”]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi

2、配置环境变量
[oracle@localhost.localdomain:/soft]$vi ~/.bash_profile

3、查询配置后信息

[oracle@localhost.localdomain:/soft]$cat ~/.bash_profile
# .bash_profile

# Get the aliases and functions
if [-f ~/.bashrc]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

# Oracle Settings oracle
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
PS1=”[`whoami`@`hostname`:”‘$PWD]$’
alias sqlplus=”rlwrap sqlplus”
alias rman=”rlwrap rman”
alias alert=”cd /oracle/app/oracle/diag/rdbms/db01/db01/trace”
alias lsnrctl=”rlwrap lsnrctl”
alias adrci=”rlwrap adrci”
export LANG=en_US
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0; export ORACLE_HOME
ORACLE_SID=db01; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
NLS_DATE_FORMAT=”yyyy-mm-dd HH24:MI:SS”; export NLS_DATE_FORMAT
#NLS_LANG=AMERICAN_AMERICA.ZHS16GBK;export NLS_LANG
NLS_LANG=AMERICAN_AMERICA.AL16UTF16;export NLS_LANG

if [$USER = “oracle”] || [$USER = “grid”]; then
if [$SHELL = “/bin/ksh”]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi

4、配置后可以通过 SQL 查询修改后字符集是否变更

select * from nls_database_parameters;

5、再次重启导入数据

[oracle@localhost.localdomain:/soft]$imp scott/scott file=/soft/scott_v1.dmp log=/soft/scott_v1.log1 ignore=y full=y

Import: Release 11.2.0.4.0 – Production on Sun Oct 22 16:46:17 2017

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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 “BONUS” 0 rows imported
. . importing table “DEPT” 4 rows imported
. . importing table “EMP” 14 rows imported
. . importing table “SALGRADE” 5 rows imported
About to enable constraints…
Import terminated successfully without warnings.– 此时导入比较成功，没有报错了

知识延伸阅读

测试的时候，本机 oracle 安装采用了 utf8 字符集，而项目的要求是 gbk 字符集，为了防止以后有不同字符集数据信息导入导出的问题，整理以下文档。
修改 oracle 字符集新装了 oracle, 装为 AL32UTF8 格式, 无奈一个工程导出包是 ZHS16GBK 格式, 想了想办法转换, 以下是学习
一、什么是 oracle 字符集
Oracle 字符集是一个字节数据的解释的符号集合, 有大小之分, 有相互的包容关系。ORACLE 支持国家语言的体系结构允许你使用本地化语言来存储，处理，检索数据。它使数据库工具，错误消息，排序次序，日期，时间，货币，数字，和日历自动适应本地化语言和平台。

影响 oracle 数据库字符集最重要的参数是 NLS_LANG 参数。它的格式如下:

NLS_LANG = language_territory.charset

它有三个组成部分(语言、地域和字符集)，每个成分控制了 NLS 子集的特性。其中:

Language 指定服务器消息的语言，territory 指定服务器的日期和数字格式，charset 指定字符集。如:AMERICAN _ AMERICA. ZHS16GBK

从 NLS_LANG 的组成我们可以看出，真正影响数据库字符集的其实是第三部分。所以两个数据库之间的字符集只要第三部分一样就可以相互导入导出数据，前面影响的只是提示信息是中文还是英文。

二、如何查询 Oracle 的字符集

很多人都碰到过因为字符集不同而使数据导入失败的情况。这涉及三方面的字符集，一是 oracel server 端的字符集，二是 oracle client 端的字符集; 三是 dmp 文件的字符集。在做数据导入的时候，需要这三个字符集都一致才能正确导入。开始—运行—sqlplus，用户名输入：system as sysdba
密码：XXXX

1、查询 oracle server 端的字符集

有很多种方法可以查出 oracle server 端的字符集，比较直观的查询方法是以下这种:SQL>select userenv(‘language’) from dual;

结果类似如下:AMERICAN _ AMERICA. ZHS16GBK（本机结果 SIMPLIFIED CHINESE_CHINA.AL32UTF8）

2、如何查询 dmp 文件的字符集

用 oracle 的 exp 工具导出的 dmp 文件也包含了字符集信息，dmp 文件的第 2 和第 3 个字节记录了 dmp 文件的字符集。如果 dmp 文件不大，比如只有几 M 或几十 M，可以用 UltraEdit 打开(16 进制方式)，看第 2 第 3 个字节的内容，如 0354，然后用以下 SQL 查出它对应的字符集:

SQL> select nls_charset_name(to_number(‘0354′,’xxxx’)) from dual;

ZHS16GBK

如果 dmp 文件很大，比如有 2G 以上(这也是最常见的情况)，用文本编辑器打开很慢或者完全打不开，可以用以下命令(在 unix 主机上):

cat exp.dmp |od -x|head -1|awk ‘{print $2 $3}’|cut -c 3-6

然后用上述 SQL 也可以得到它对应的字符集。

3、查询 oracle client 端的字符集

这个比较简单。在 windows 平台下，就是注册表里面 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\NLS_LANG。还可以在 dos 窗口里面自己设置，比如:

set nls_lang=AMERICAN_AMERICA.ZHS16GBK

这样就只影响这个窗口里面的环境变量。

在 unix 平台下，就是环境变量 NLS_LANG。

$echo $NLS_LANG

AMERICAN_AMERICA.ZHS16GBK

如果检查的结果发现 server 端与 client 端字符集不一致，请统一修改为同 server 端相同的字符集。

三、修改 oracle 的字符集

上文说过，oracle 的字符集有互相的包容关系。如 us7ascii 就是 zhs16gbk 的子集, 从 us7ascii 到 zhs16gbk 不会有数据解释上的问题, 不会有数据丢失。在所有的字符集中 utf8 应该是最大, 因为它基于 unicode, 双字节保存字符(也因此在存储空间上占用更多)。

一旦数据库创建后，数据库的字符集理论上讲是不能改变的。因此，在设计和安装之初考虑使用哪一种字符集十分重要。根据 Oracle 的官方说明，字符集的转换是从子集到超集受支持, 反之不行。如果两种字符集之间根本没有子集和超集的关系，那么字符集的转换是不受 oracle 支持的。对数据库 server 而言，错误的修改字符集将会导致很多不可测的后果，可能会严重影响数据库的正常运行，所以在修改之前一定要确认两种字符集是否存在子集和超集的关系。一般来说，除非万不得已，我们不建议修改 oracle 数据库 server 端的字符集。特别说明，我们最常用的两种字符集 ZHS16GBK 和 ZHS16CGB231280 之间不存在子集和超集关系，因此理论上讲这两种字符集之间的相互转换不受支持。

1、修改 server 端字符集(不建议使用)

在 oracle 8 之前，可以用直接修改数据字典表 props$ 来改变数据库的字符集。但 oracle8 之后，至少有三张系统表记录了数据库字符集的信息，只改 props$ 表并不完全，可能引起严重的后果。正确的修改方法如下:

$sqlplus /nolog

SQL>conn / as sysdba;

以上方法测试不行，用 scott/tiger 登陆 sqlplus 然后 connect sys/sys as sysdba，然后输入命令即可

若此时数据库服务器已启动，则先执行 SHUTDOWN IMMEDIATE 命令关闭数据库服务器，然后执行以下命令:

SQL>STARTUP MOUNT;

SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;

SQL>ALTER DATABASE OPEN;

SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; // 跳过超子集检测

SQL>ALTER DATABASE national CHARACTER SET INTERNAL ZHS16GBK;

这一行不起作用，执行后出错 ORA-00933: SQL 命令未正确结束，不过执行上一行命令已经生效，其他文章里未提到本行。

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP

2、修改 dmp 文件字符集

上文说过，dmp 文件的第 2 第 3 字节记录了字符集信息，因此直接修改 dmp 文件的第 2 第 3 字节的内容就可以‘骗’过 oracle 的检查。这样做理论上也仅是从子集到超集可以修改，但很多情况下在没有子集和超集关系的情况下也可以修改，我们常用的一些字符集，如 US7ASCII，WE8ISO8859P1，ZHS16CGB231280，ZHS16GBK 基本都可以改。因为改的只是 dmp 文件，所以影响不大。

具体的修改方法比较多，最简单的就是直接用 UltraEdit 修改 dmp 文件的第 2 和第 3 个字节。比如想将 dmp 文件的字符集改为 ZHS16GBK，可以用以下 SQL 查出该种字符集对应的 16 进制代码:

SQL> select to_char(nls_charset_id(‘ZHS16GBK’), ‘xxxx’) from dual;

0354

然后将 dmp 文件的 2、3 字节修改为 0354 即可。

如果 dmp 文件很大，用 ue 无法打开，就需要用程序的方法了。网上有人用 java 存储过程写了转换的程序(用 java 存储过程的好处是通用性教好，缺点是比较麻烦)。我在 windows 下测试通过。但要求 oracle 数据库一定要安装 JVM 选项。有兴趣的朋友可以研究一下程序代码

在注册表中更改 ORACLE 的字符集编码方式的操作：regedit
注册表路径：HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
把 NLS_LANG 的值从 SIMPLIFIED CHINESE_CHINA.ZHS16GBK
改为 AMERICAN_AMERICA.US7ASCII american_america.we8dec
PS:
Oracle UTF8 编码问题
其实这是一个老问题，处理过多次了，每次都没有记住，这次决定写下来了。其实很简单，
1）Oracle 其实非常好，它会自动处理编码的转换。
2）客户端（输入数据的机器）保证自结数据不出错就好了。
在这个基础上，要用 Oracle, 并且用 UTF8（多数情况下是为了应付多语言）存储数据，只需要做两件事情：
a)Oracle 数据库的实例的编码设置为 UTF8;(NLS_LANG=AMERICAN _ AMERICA.UTF8)
b)数据文件是什么编码，就将客户端设置为什么编码（例如 NLS_LANG=AMERICAN _ AMERICA.ZHS16GBK 在注册表中修改），然后用工具导入数据。
就可以了 – 在 Java 程序中可以尽情使用 UTF8 来显示中日韩等多国语言了

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2018-01/150043.htm

正文完

星哥说事-微信公众号