012013
 

Oracle imp/exp导入导出文件的编码

环境:
源数据库:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
源系统:Window 2003 R2
目标数据库:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
目标系统:CentOS release 6.4 (Final)

需求:将源数据库中HTIMS用户所有数据导入到目标数据库中。
实现方式:
在源系统上执行EXP HTIMS/HTIMS@QCJK FULL=Y FILE=HTIMS_2013_08_01.dmp LOG=IMP.LOG
在目标系统上执行imp HTIMS/HTIMS@DB11G FULL=Y FILE=HTIMS_2013_08_01.dmp LOG=IMP.LOG

问题:导入时因编码问题中文字段名编程了?
IMP-00017: following statement failed with ORACLE error 957:
“CREATE FORCE VIEW “HTIMS”.”VIEW_BASE_OFFICE” (“??”
“”,”??”,”GEOMETRY”,”????”,”?????”,”???”,”?????”,”??”,”ORG”,”????”) AS ”
“SELECT Bc.Office_Id,”
” Bc.Office_Code,”
” BC.GEOMETRY,”
” ‘???’,”
” bc.OFFICE_NAME,”
” bc.LINKMAN,”
” bc.LINKPHONE,”
” bc.BRIEF,”
” SO.CODE,”
” SO.NAME”
” FROM base_office bc ,”
” sys_org so”
” WHERE bc.sys_org_id = so.sys_org_id”
” AND SO.DEL_FLG = 0″
” AND bc.geometry IS NOT NULL”
” AND BC.DEL_FLG = ’0′”
IMP-00003: ORACLE error 957 encountered
ORA-00957: duplicate column name

看上面的错误日志,矛头都指向了ORA-00957:列名重复,看看它执行的语句:中文列名变成了“?”。

问题分析:
首先确定各环节编码:
源Oracle编码:ZHS16GBK
exp客户端编码:ZHS16GBK
目标Oracle编码:WE8MSWIN1252
imp客户端编码:US7ASCII and AL16UTF16 NCHAR

查询数据库编码:
SQL> select * from nls_database_parameters where parameter =’NLS_CHARACTERSET’;
PARAMETER VALUE
NLS_CHARACTERSET ZHS16GBK

exp/imp客户端编码是通过执行imp导入dmp文件时打印的日志中看到的,看下面日志:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.01.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)

在上面的导入日志中可以看到这一句:possible charset conversion,这句话的含义就是imp在执行导入数据任务时会自动将源数据编码转换为目标数据编码,
由此分析它的转换流程如下:
源数据库编码==>Exp客户端编码==>Imp客户端编码==>目标数据库编码
ZHS16GBK ==>ZHS16GBK ==>US7ASCII ==>WE8MSWIN1252

可见这一次导入作业imp需要将数据进行两次转码,先由ZHS16GBK到US7ASCII在到WE8MSWIN1252

咱们的数据就是在由ZHS16GBK转换为US7ASCII时出现了乱码,因为US7ASCII字符集不是ZHS16GBK的超集,所以出现乱码。

解决问题,统一字符集:
修改imp客户端编码
[oracle@lg-centos ~]$ export NLS_LANG=”Simplified Chinese_china”.ZHS16GBK

修改目标数据库字符集
sqlplus / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 941600768 bytes
Fixed Size 1340440 bytes
Variable Size 692063208 bytes
Database Buffers 243269632 bytes
Redo Buffers 4927488 bytes
Database mounted.

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
–提示新字符集必须为旧字符集的超集

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 941600768 bytes
Fixed Size 1340440 bytes
Variable Size 692063208 bytes
Database Buffers 243269632 bytes
Redo Buffers 4927488 bytes
Database mounted.
Database opened.

–查看字符集
select * from v$nls_parameters;

最终导入成功:

[oracle@lg-centos ~]$ imp HTIMS/HTIMS@DB11G FULL=Y FILE=HTIMS_2013_08_01.dmp LOG=IMP.LOG COMMIT=Y BUFFER=40960000

Import: Release 11.2.0.1.0 – Production on 1 17:53:08 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.01.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

对不起,评论功能目前被关闭。