【exp/imp】将US7ASCII字符集的dmp文件导入到ZHS16GBK字符集的数据库中
1.1 BLOG文档结构图
1.2 前言部分
1.2.1 导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 如何将US7ASCII字符集的dmp文件导入到ZHS16GBK字符集的数据库中（重点，2种方法）？
② 从dmp文件可以获取到哪些信息？如何从dmp文件获取到dmp文件的字符集（重点，N种方法）？
③ 如何从dmp文件中获取到其中的DDL语句，例如建表、建索引语句等（2种方法）
④ dmp文件导入的一般步骤
⑤ imp工具的indexfile选项的作用
⑥ 软件UE、EditPlus、Pilotedit软件的使用
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和微信公众号（xiaomaimiaolhr）上有同步更新。
② 文章中用到的所有代码、相关软件、相关资料及本文的pdf版本都请前往小麦苗的云盘下载，小麦苗的云盘地址见：http://blog.itpub.net/26736162/viewspace-1624453/。
③ 若网页文章代码格式有错乱，请下载pdf格式的文档来阅读。
④ 在本篇BLOG中，代码输出部分一般放在一行一列的表格中。
⑤ 本文适合于Oracle初中级人员阅读，Oracle大师请略过本文。
本文若有错误或不完善的地方请大家多多指正，您的批评指正是我写作的最大动力。
1.3 本文相关知识点
1.3.1 可以从dmp文件获取哪些信息？
在开发中常常碰到，需要导入dmp文件到现有数据库。这里的dmp文件可能来自于其它系统，所以，一般情况下是不知道导出程序（exp）的版本、导出时间或者导出模式等信息的。那么如何从现有的dmp文件中获取到这些信息呢？下面作者将一一讲解。
1.3.1.1 获取基本信息：导出的版本、时间、导出的用户
下面的示例中exp_ddl_lhr_02.dmp是生成的dmp文件：
| [ZFZHLHRDB1:oracle]:/tmp>strings exp_ddl_lhr_02.dmp | head -10 TEXPORT:V11.02.00 ====》版本号 DSYS ====》使用SYS用户导出 RTABLES ====》基于表模式导出，RUSERS表示基于用户模式,RENTIRE表示基于全库模式 4096 Tue Aug 2 16:8:8 2016/tmp/exp_ddl_lhr_02.dmp====》生成的时间和文件地址 #C#G #C#G +00:00 BYTE UNUSED
|
1.3.1.2 获取dmp文件中的表信息
下面的示例中，exp_ddl_lhr_02.dmp是生成的dmp文件：
| [ZFZHLHRDB1:oracle]:/tmp>strings exp_ddl_lhr_02.dmp | grep "CREATE TABLE"|awk '{print $3}'|sed 's/"//g' EMP ====》说明exp_ddl_lhr_02.dmp中只有一个emp表 |
1.3.1.3 解析dmp文件生成parfile文件
下面的示例中，exp_ddl_lhr_03.dmp是生成的dmp文件：
| [ZFZHLHRDB1:oracle]:/tmp>strings exp_ddl_lhr_03.dmp | grep "CREATE TABLE"|awk '{print $3}'|sed 's/"//g'|awk '{ if (FNR==1) print "tables="$1 ; else print ","$1 }' tables=DEF$_AQCALL ,DEF$_AQERROR ,DEF$_CALLDEST ,DEF$_DEFAULTDEST ,DEF$_DESTINATION ,DEF$_ERROR ,DEF$_LOB ,DEF$_ORIGIN ,DEF$_PROPAGATOR ,DEF$_PUSHED_TRANSACTIONS ,MVIEW$_ADV_INDEX [ZFZHLHRDB1:oracle]:/tmp>
|
1.3.1.4 如何查看dmp文件的字符集
一、 imp导入命令查看有2种办法可以查看dmp文件的字符集，第一种办法为imp导入命令查看，示例如下所示：
| [ZFLHRZHDB1:oracle]:/oracle>ORACLE_SID=lhrdb [ZFLHRZHDB1:oracle]:/oracle>export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 [ZFLHRZHDB1:oracle]:/oracle>exp \'/ AS SYSDBA\' tables=scott.emp file=/tmp/exp_ddl_lhr_03.dmp log=/tmp/exp_table.log buffer=41943040 rows=n compress=n Export: Release 11.2.0.4.0 - Production on Tue Oct 25 17:14:49 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Tes Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set<<<--当前的NLS_LANG环境变量的值，即生成的dmp文件的字符集 server uses ZHS16GBK character set (possible charset conversion)<<<<<<<--当前数据库的字符集 Note: table data (rows) will not be exported About to export specified tables via Conventional Path ... Current user changed to SCOTT . . exporting table EMP EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings. [ZFLHRZHDB1:oracle]:/oracle>ORACLE_SID=mydb <<---更换数据库 [ZFLHRZHDB1:oracle]:/oracle>export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK [ZFLHRZHDB1:oracle]:/oracle>imp \'/ AS SYSDBA\' tables=xxx.xx file=/tmp/exp_ddl_lhr_03.dmp Import: Release 11.2.0.4.0 - Production on Tue Oct 25 16:27:15 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Tes Export file created by EXPORT:V11.02.00 via conventional path<<<<<<<<<----dmp文件的导出版本号 import done in ZHS16GBK character set and AL16UTF16 NCHAR character set<<<<<<--当前的NLS_LANG环境变量的值 import server uses WE8ISO8859P1 character set (possible charset conversion)<<<<<<---当前数据库的字符集 export client uses AL32UTF8 character set (possible charset conversion)<<<<<<--dmp文件的字符集 IMP-00029: cannot qualify table name by owner (xxx.xx), use FROMUSER parameter IMP-00000: Import terminated unsuccessfully
|
如果NLS_LANG的值和当前数据库的字符集相同，那么将不显示“server uses”和“import server uses”行。如果没有显示“export client”行，那么说明当前dmp文件的字符集和当前的NLS_LANG环境变量的值相同。无论是使用exp还是imp工具都会显示当前的NLS_LANG环境变量的值（表现为“Export done”、“import done”）。
二、 十六进制的第2和第3个字节第二种查看dmp文件字符集的办法是，以十六进制的方式打开dmp文件，然后查看第2和第3个字节。如下所示：
| [ZFLHRZHDB1:oracle]:/oracle>cat /tmp/exp_ddl_lhr_03.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 1-2,7-8 0369 [ZFLHRZHDB1:oracle]:/oracle>cat /tmp/exp_ddl_lhr_03.dmp |od -x|head -1 0000000 0303 4569 5058 524f 3a54 3156 2e30 3230 [oracle@rhel6lhr env_oracle]$
|
然后在数据库中可以查到十六进制0369代表的字符集：
| SYS@lhrdb> SELECT NLS_CHARSET_NAME(TO_NUMBER('0369','XXXX')) FROM DUAL; NLS_CHARSET_NAME(TO_NUMBER('0369','XXXX' ---------------------------------------- AL32UTF8
|
以上结果说明dmp文件的字符集是UTF8。若dmp文件在Windows平台下，则可以使用软件UltraEdit（UE）、EditPlus或Pilotedit等文本编辑工具以十六进制的方式打开dmp文件查看。其中，软件Pilotedit可以轻松打开上G的文件。示例如下：
需要注意的是，十六进制在Linux和Windows下顺序不同。
SELECT NLS_CHARSET_NAME(TO_NUMBER('0001', 'XXXX')) US7ASCII,
NLS_CHARSET_NAME(TO_NUMBER('0354', 'XXXX')) ZHS16GBK,
NLS_CHARSET_NAME(TO_NUMBER('0369', 'XXXX')) AL32UTF8,
TO_CHAR(NLS_CHARSET_ID('US7ASCII'), 'XXXX') US7ASCII_ID,
TO_CHAR(NLS_CHARSET_ID('ZHS16GBK'), 'XXXX') ZHS16GBK_ID,
TO_CHAR(NLS_CHARSET_ID('AL32UTF8'), 'XXXX') AL32UTF8_ID
FROM DUAL;
| SYS@ora10g> SELECT NLS_CHARSET_NAME(TO_NUMBER('0001', 'XXXX')) US7ASCII, 2 NLS_CHARSET_NAME(TO_NUMBER('0354', 'XXXX')) ZHS16GBK, 3 NLS_CHARSET_NAME(TO_NUMBER('0369', 'XXXX')) AL32UTF8, 4 TO_CHAR(NLS_CHARSET_ID('US7ASCII'), 'XXXX') , 5 TO_CHAR(NLS_CHARSET_ID('ZHS16GBK'), 'XXXX') , 6 TO_CHAR(NLS_CHARSET_ID('AL32UTF8'), 'XXXX') 7 FROM DUAL;
US7ASCII ZHS16GBK AL32UTF8 TO_CH TO_CH TO_CH ---------------------------------------- ---------------------------------------- ---------------------------------------- ----- ----- ----- US7ASCII ZHS16GBK AL32UTF8 1 354 369
SYS@ora10g>
|
1.3.2 如何获取数据库DDL的创建语句
数据泵工具（impdp）工具给我们提供了SQLFILE的命令行选项，只获取DDL语句，并未真正的执行数据导入。另外，若单纯为了导出DDL语句则可以在使用expdp导出的时候使用CONTENT=METADATA_ONLY和EXCLUDE=STATISTICS选项，这样导出的DMP文件比较小。如下所示：
|
expdp \'/ AS SYSDBA\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=lhrsql20161215.dmp LOGFILE=lhrsql20161215.log CONTENT=METADATA_ONLY SCHEMAS=SCOTT EXCLUDE=STATISTICS impdp \'/ AS SYSDBA\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=lhrsql20161215.dmp LOGFILE=imp_exptest.log SQLFILE=expddl_lhr.sql
|
查看expddl_lhr.sql文件即可获取DDL语句。
imp工具使用SHOW=Y LOG=GET_DDL.sql的方式，可以看到清晰的DDL脚本，同时也不会真正的执行数据导入。另外，若单纯为了导出DDL语句则可以在使用exp导出的时候使用ROWS=N选项，这样导出的DMP文件比较小。如下所示：
| exp \'/ AS SYSDBA\' TABLES=SCOTT.EMP FILE=/tmp/exp_ddl_lhr_01.dmp LOG=/tmp/exp_table.log BUFFER=41943040 ROWS=N COMPRESS=N imp \'/ AS SYSDBA\' FILE=/tmp/exp_ddl_lhr_01.dmp SHOW=Y LOG=/tmp/get_ddl.sql BUFFER=20480000 FULL=Y
|
查看get_ddl.sql文件即可获取DDL语句。
| ---- 生成DDL语句不会导入数据 --expdp \'/ AS SYSDBA\' tables=lhr.exptest directory=DATA_PUMP_DIR dumpfile=exptest.dmp logfile=exp_exptest.dmp EXCLUDE=STATISTICS --expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=lhrsql20161215.dmp logfile=lhrsql20161215.log content=metadata_only schemas=SCOTT EXCLUDE=STATISTICS impdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=lhrsql20161215.dmp logfile=imp_exptest.log sqlfile=exptest.sql
exp \'/ AS SYSDBA\' tables=scott.emp file=/tmp/exp_ddl_lhr_01.dmp log=/tmp/exp_table.log buffer=41943040 rows=n compress=n imp \'/ AS SYSDBA\' file=/tmp/exp_ddl_lhr_01.dmp show=y log=/tmp/get_ddl.sql buffer=20480000 full=y
|
1.3.2.1 imp示例：
| [ZFZHLHRDB1:oracle]:/oracle>exp \'/ AS SYSDBA\' tables=scott.emp file=/tmp/exp_ddl_lhr_01.dmp log=/tmp/exp_table.log buffer=41943040 rows=n compress=n
Export: Release 11.2.0.4.0 - Production on Tue Aug 2 15:42:11 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Tes Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ... Current user changed to SCOTT . . exporting table EMP Export terminated successfully without warnings. [ZFZHLHRDB1:oracle]:/oracle>imp \'/ AS SYSDBA\' file=/tmp/exp_ddl_lhr_01.dmp show=y log=/tmp/get_ddl.sql buffer=20480000 full=y
Import: Release 11.2.0.4.0 - Production on Tue Aug 2 15:42:44 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Tes
Export file created by EXPORT:V11.02.00 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing SYS's objects into SYS . importing SCOTT's objects into SCOTT "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"" "CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCH" "AR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUM" "BER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN" "S 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST " "GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS" "CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAX" "TRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREEL" "IST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING" "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"" "ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDE" "X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MIN" "EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "US" "ERS" LOGGING ENABLE " "ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN" "CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE" "ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO"" Import terminated successfully without warnings. [ZFZHLHRDB1:oracle]:/oracle>
|
由于格式比较混乱，直接运行会报错，建荣的书中给了一段代码来格式化：
| [ZFZHLHRDB1:oracle]:/tmp>more /tmp/get_ddl.sql
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Tes
Export file created by EXPORT:V11.02.00 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing SYS's objects into SYS . importing SCOTT's objects into SCOTT "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"" "CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCH" "AR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUM" "BER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN" "S 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST " "GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS" "CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAX" "TRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREEL" "IST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING" "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"" "ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDE" "X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MIN" "EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "US" "ERS" LOGGING ENABLE " "ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN" "CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE" "ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO"" Import terminated successfully without warnings. [ZFZHLHRDB1:oracle]:/tmp>more /tmp/gettabddl.sh awk ' / \"BEGIN / { N=1; } / \"CREATE / { N=1; } / \"CREATE INDEX/ { N=1; } / \"CREATE UNIQUE INDEX/ { N=1; } / \"ALTER / { N=1; } / \" ALTER / { N=1; } / \"ANALYZE / { N=1; } / \"GRANT / { N=1; } / \"COMMENT / { N=1; } / \"AUDIT / { N=1; } N==1 { printf "\n/\n"; N++ } /\"$/ { if (N==0) next; s=index( $0, "\"" ); ln0=length( $0 ) if ( s!=0 ) { lcnt++ if ( lcnt >= 30 ) { ln=substr( $0,s+1,length( substr($0,s+1))-1) t=index( ln, ")," ) if ( t==0 ) { t=index( ln, ", " ) } if ( t==0 ) { t=index( ln, ") " ) } if ( t > 0 ) { printf "%s\n%s",substr( ln,1,t+1), substr(ln, t+2) lcnt=0 } else { printf "%s", ln if ( ln0 < 78 ) { printf "\n" ; lcnt=0 } } } else { printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 ) if ( ln0 < 78 ) { printf "\n" ; lcnt=0 } } } } END { printf "\n/\n"} ' $* |sed '1,2d; /^$/ d; s/STORAGE *(INI/~ STORAGE (INI/g; s/, "/,~ "/g; s/ (\"/~ &/g; s/PCT[FI]/~ &/g; s/[( ]PARTITION /~&/g; s/) TABLESPACE/)~ TABLESPACE/g; s/ , / ,~/g; s/ DATAFILE /&~/' | tr "~" "\n" [ZFZHLHRDB1:oracle]:/tmp> [ZFZHLHRDB1:oracle]:/tmp>ksh /tmp/gettabddl.sh /tmp/get_ddl.sql > /tmp/gen_tabddl.sql [ZFZHLHRDB1:oracle]:/tmp>more /tmp/gen_tabddl.sql ALTER SESSION SET CURRENT_SCHEMA= "SCOTT" / CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS / CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING / ALTER SESSION SET CURRENT_SCHEMA= "SCOTT" / ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE / ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE / ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO" / [ZFZHLHRDB1:oracle]:/tmp>
|
这样运行起来就方便多了。
1.3.2.2 imp的indexfile选项（indexfile导出表和索引的ddl语句）
exp和imp工具中可能存在把table从一个库exp然后imp到另一个数据库出现没有指定tablespace而无法imp，imp的indexfile参数中可以解决的。
Oracle的imp工具指定indexfile参数后，可以不导入任何对象，而只把需要创建的index以sql语句的形式写入文本文件。创建库表等sql语句也会写入，但用rem注释屏蔽。
一、查看并修改导入对象的存储参数
如果原始库中有些表比较大，exp导出对象的初始存储空间设置可能比较高，导入时需要先申请分配较大的存储空间，如果只进行逻辑结构的迁移耗时较长。这时可以用indexfile参数导出sql语句，筛选出初始空间较高的建表语句，手工创建。再次导入时使用ignore选项忽略对象创建错误。
如何解析inexfile文件：可以考虑用sed编辑器进行正则表达式替换，也可以写个程序解析出initial超出一定阈值的库表及其sql。
示例如下所示：
| [oracle@rhel6lhr tmp]$ exp \'/ AS SYSDBA\' TABLES=SCOTT.EMP FILE=/tmp/exp_ddl_lhr_01.dmp LOG=/tmp/exp_table.log BUFFER=41943040 ROWS=N COMPRESS=N
Export: Release 11.2.0.3.0 - Production on Wed May 3 21:36:47 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ... Current user changed to SCOTT . . exporting table EMP Export terminated successfully without warnings. [oracle@rhel6lhr tmp]$ imp \'/ AS SYSDBA\' file=/tmp/exp_ddl_lhr_01.dmp FULL=Y indexfile=/tmp/get_ti_ddl.sql rows=n
Import: Release 11.2.0.3.0 - Production on Wed May 3 21:38:10 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, 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 terminated successfully without warnings. [oracle@rhel6lhr tmp]$ more /tmp/get_ti_ddl.sql
REM CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME" REM VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, REM "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 REM NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL REM DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS ; CONNECT SCOTT; CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ; REM ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY REM ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 REM STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE ; REM ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY REM ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE ; REM ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "FK_DEPTNO" ; [oracle@rhel6lhr tmp]$
|
可以看到其中的创建表的SQL语句被注释掉了，这个可以用vi命令或者文本工具来处理，处理之后就可以直接使用了。
1.3.2.3 impdp示例：
| --expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp impdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql
|
| [ZFZHLHRDB1:oracle]:/oracle>expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp
Export: Release 11.2.0.4.0 - Production on Wed Aug 3 15:14:55 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 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/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.562 KB 14 rows . . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . exported "SCOTT"."TEST" 5.007 KB 1 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /oracle/app/oracle/admin/lhrdb/dpdump/exptest_sql.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 3 15:15:16 2016 elapsed 0 00:00:20
[ZFZHLHRDB1:oracle]:/oracle>impdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql
Import: Release 11.2.0.4.0 - Production on Wed Aug 3 15:16:06 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_SQL_FILE_FULL_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql 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/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Wed Aug 3 15:16:09 2016 elapsed 0 00:00:02
[ZFZHLHRDB1:oracle]:/oracle>cd /oracle/app/oracle/admin/lhrdb/dpdump/ [ZFZHLHRDB1:oracle]:/oracle/app/oracle/admin/lhrdb/dpdump>more exptest.sql -- CONNECT SYS ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 '; -- new object type path: SCHEMA_EXPORT/USER -- CONNECT SYSTEM CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:268AB71B15071D81F19C6FC5041FA8F8E49397470FFE05458B8C90D9E7F8;F894844C34402B67' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK; -- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT GRANT UNLIMITED TABLESPACE TO "SCOTT"; -- new object type path: SCHEMA_EXPORT/ROLE_GRANT GRANT "CONNECT" TO "SCOTT"; GRANT "RESOURCE" TO "SCOTT"; -- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE ALTER USER "SCOTT" DEFAULT ROLE ALL; -- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA -- CONNECT SCOTT
BEGIN sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'LHRDB', inst_scn=>'4225469'); COMMIT; END; / -- new object type path: SCHEMA_EXPORT/TABLE/TABLE -- CONNECT SYS CREATE TABLE "SCOTT"."DEPT" ( "DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14 BYTE), "LOC" VARCHAR2(13 BYTE) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; CREATE TABLE "SCOTT"."EMP" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; CREATE TABLE "SCOTT"."BONUS" ( "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "SAL" NUMBER, "COMM" NUMBER ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" ; CREATE TABLE "SCOTT"."SALGRADE" ( "GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; CREATE TABLE "SCOTT"."TEST" ( "DUMMY" VARCHAR2(1 BYTE) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX -- CONNECT SCOTT CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "SCOTT"."PK_DEPT" NOPARALLEL; CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "SCOTT"."PK_EMP" NOPARALLEL; -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT -- CONNECT SYS ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING INDEX "SCOTT"."PK_DEPT" ENABLE; ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX "SCOTT"."PK_EMP" ENABLE; -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5, :6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; i_n := 'PK_DEPT'; i_o := 'SCOTT'; EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,4,1,4,1,1,1,0,4,NV,NV,TO_DATE('2016-07-07 22:00:11',df),NV;
DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; END; / 《《《《。。。。。。。。篇幅原因，有省略,剩下的都是统计信息，生成sqlfile的时候也可以不用生成。。。。。。。。》》》》
|
-------------------------------------------------------------------------
1.4 本文简介
一个网友找到我说，一个dmp文件导入数据库中，中文一直是乱码，看我能否帮忙解决一下。说真心话，一般情况下，乱码问题和安装问题，我一般不想接手，因为可能很简单的问题，有的人懒的动脑，碰到问题就问。尤其对于安装类问题，照着安装文档，一步一步来，一般都没有问题。在这里把一张网友分享的图片再分享一下：
可是，问字符集的的哥们，我能感觉到他自己是下了功夫的，都是自己摸索了，实在解决不了，才找到的我。这种情况下，我果断是要帮助的。好了，废话不多说了，且看整个处理过程吧。
1.4.1 本文实验环境介绍
| 项目 |
source db |
target db |
| db 类型 |
|
|
| db version |
10.2.0.1.0 |
10.2.0.1.0 |
| db 存储 |
|
|
| OS版本及kernel版本 |
|
|
| 字符集 |
US7ASCII |
GBK |
| dmp文件字符集 |
US7ASCII |
US7ASCII |
1.5 开始导入
1.5.1 首先获取dmp文件的相关信息
网友给的dmp文件：
大约30M，解压后有282M左右：
| [oracle@rhel6lhr ~]$ strings /tmp/hhris.dmp | head -10 EXPORT:V10.02.01 DHHRIS RUSERS 8192 Wed Oct 16 5:0:14 2013/data/dbbackup/expdata/hhris.dmp #G#G #G#G +08:00 BYTE UNUSED [oracle@rhel6lhr ~]$ strings /tmp/hhris.dmp | grep "CREATE TABLE"|awk '{print $3}'|sed 's/"//g' ADDTOHIS APPOINT APPOINTDETAIL APPOINTMASTER BACKUP_HISAPPOINT BACKUP_R_DIAGNOSES BACKUP_R_SERIES BACKUP_R_STUDIES DICT_CAPTION DICT_CITY DICT_CLASS DICT_CLASSRULE 《《《《。。。。。。。。篇幅原因，有省略。。。。。。。。》》》》 USER_PARAM USER_RIS USER_WEB WEB_LOG WEB_USER WORK_FLOW WORK_NODE [oracle@rhel6lhr ~]$ [oracle@rhel6lhr ~]$ cat /tmp/hhris.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 1-2,7-8 0001 SYS@ora10g> SELECT NLS_CHARSET_NAME(TO_NUMBER('0001','XXXX')) FROM DUAL;
NLS_CHARSET_NAME(TO_NUMBER('0001','XXXX')) ------------------------------------------------------------------------------------------------------------------------ US7ASCII
|
可以得出以下结论：
1、dmp文件是由10.02.01的客户端导出的
2、基于HHRIS用户导出
3、该用户下有很多表
4、dmp文件的字符集是US7ASCII
1.5.2 找出dmp文件的DDL语句
主要查看是否有其它表空间导致不能导入的问题。
| [oracle@rhel6lhr env_oracle]$ imp \'/ AS SYSDBA\' file=/tmp/hhris.dmp show=y log=/tmp/get_ddl.sql buffer=20480000 full=y
Import: Release 10.2.0.1.0 - Production on Tue May 9 14:06:22 2017
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via direct path
Warning: the objects were exported by HHRIS, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set export client uses US7ASCII character set (possible charset conversion) . importing HHRIS's objects into SYS "BEGIN " "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','" 《《《《。。。。。。。。篇幅原因，有省略。。。。。。。。》》》》
[oracle@rhel6lhr env_oracle]$ sh gettabdd.sh /tmp/get_ddl.sql > /tmp/gen_tabddl.sql [oracle@rhel6lhr env_oracle]$ more /tmp/gen_tabddl.sql BEGIN sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'ORACLE', inst_scn=>'59161085'); COMMIT; END; / CREATE SEQUENCE "MICROSOFTSEQDTPROPERTIES" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 50 NOORDER NOCYCLE / CREATE SEQUENCE "R_REPORTLOG_LOGID" MINVALUE 1 MAXVALUE 100000 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER CYCLE / 。。。。。。。。。。。。。 《《《《。。。。。。。。篇幅原因，有省略。。。。。。。。》》》》
|
查找关键字tablespace，发现只有1个表空间HHRIS。
1.5.3 数据库准备
| dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \ -gdbname lhrdb -sid lhrdb \ -sysPassword lhr -systemPassword lhr \ -datafileDestination '/cds/oradata' -recoveryAreaDestination '/cds/oradata' \ -storageType FS \ -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \ -sampleSchema false \ -memoryPercentage 20 \ -databaseType OLTP \ -emConfiguration NONE ORACLE_SID=lhrdb export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK sqlplus / as sysdba CREATE TABLESPACE HHRIS DATAFILE '/cds/oradata/mydg/HHRIS01.dbf' size 1G; create user hhris identified by lhr; grant dba to hhris; exit imp hhris/lhr file=/tmp/hhris.dmp full=Y log=/tmp/log_imp_hhrisgbk.dmp
|
| [oracle@rhel6lhr mydg]$ imp hhris/lhr file=/tmp/hhris.dmp full=Y log=/tmp/log_imp_hhrisgbk.dmp
Import: Release 10.2.0.1.0 - Production on Tue May 9 14:17:55 2017
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via direct path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set export client uses US7ASCII character set (possible charset conversion) . importing HHRIS's objects into HHRIS . . importing table "ADDTOHIS" 0 rows imported . . importing table "APPOINT" 0 rows imported . . importing table "APPOINTDETAIL" 0 rows imported . . importing table "APPOINTMASTER" 0 rows imported 《《《《。。。。。。。。篇幅原因，有省略。。。。。。。。》》》》
Import terminated successfully with warnings. [oracle@rhel6lhr mydg]$ [oracle@rhel6lhr mydg]$
|
可以成功导入，但是查询的时候，有中文乱码。
1.5.4 解决乱码
使用UE或Pilotedit软件，以16进制的格式打开dmp文件，修改dmp文件的第4行的第1-4个字节。
修改前：
修改后：
其实，也有资料显示需要把第一行的第2和第3字节，第4行的第1-4字节全部修改掉，如下所示：
经过小麦苗的测试，发现这3个地方全部修改掉，也可以成功导入。
修改后保存文件，上传服务器，重新导入，导入后查询，发现中文已经可以正常显示了。
1.5.5 还有一种不显示乱码的方式
还有一种不显示乱码的方式，那就是将US7ASCII字符集的dmp文件导入到US7ASCII字符集的数据库中。
| dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \ -gdbname lhrdb -sid lhrdb \ -sysPassword lhr -systemPassword lhr \ -datafileDestination '/cds/oradata' -recoveryAreaDestination '/cds/oradata' \ -storageType FS \ -characterset US7ASCII -nationalCharacterSet AL16UTF16 \ -sampleSchema false \ -memoryPercentage 20 \ -databaseType OLTP \ -emConfiguration NONE
|
| export NLS_LANG=AMERICAN_AMERICA.US7ASCII imp hhris/lhr file=/tmp/hhris.dmp full=Y log=/tmp/log_imp_hhrisgbk.dmp |
导入后，在Windows上设置客户端环境变量NLS_LANG为AMERICAN_AMERICA.US7ASCII，然后重启PL/SQL DEVELOPER软件后就可以正常显示中文了。
本来想着，这样再采用GBK的字符集导出，然后导入GBK的数据库中，结果发现这种方法行不通，始终有乱码。其实，走到这一步，还可以将数据导出成文本格式的文件，然后将文本格式的文件再导入GBK字符集的数据库中仍然是可行的。
1.6 本文总结
有种办法处理将US7ASCII字符集的dmp文件导入到ZHS16GBK字符集的数据库中的中文乱码问题。第一，修改dmp文件中代表字符集的字符。第二，导入US7ASCII字符集的库中，然后导出成文本格式，再导入到GBK的库中。
1.7 参考
1.7.1 博客
● http://www.doc88.com/p-0863578397263.html
● http://www.eygle.com/archives/2004/09/nls_character_set_05.html
● http://www.itpub.net/thread-1129133-2-1.html
● http://www.itpub.net/thread-1014160-2-1.html
● http://blog.itpub.net/26736162/viewspace-2137132/
● http://blog.itpub.net/26736162/viewspace-1760580/
● http://blog.itpub.net/26736162/viewspace-1686082/
● 【数据泵】EXPDP导出表结构(真实案例) http://blog.itpub.net/26736162/viewspace-1657828/、http://blog.itpub.net/26736162/viewspace-1662344/
About Me
...............................................................................................................................
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2138791/
● 本文博客园地址：http://www.cnblogs.com/lhrbest/p/6832707.html
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(646634621)，注明添加缘由
● 于 2017-05-09 09:00 ~ 2017-05-09 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
