【exp/imp】将US7ASCII字元集的dmp檔案導入到ZHS16GBK字元集的資料庫中
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① 如何将US7ASCII字元集的dmp檔案導入到ZHS16GBK字元集的資料庫中（重點，2種方法）？
② 從dmp檔案可以擷取到哪些資訊？如何從dmp檔案擷取到dmp檔案的字元集（重點，N種方法）？
③ 如何從dmp檔案中擷取到其中的DDL語句，例如建表、建索引語句等（2種方法）
④ dmp檔案導入的一般步驟
⑤ imp工具的indexfile選項的作用
⑥ 軟體UE、EditPlus、Pilotedit軟體的使用
在開發中常常碰到，需要導入dmp檔案到現有資料庫。這裡的dmp檔案可能來自于其它系統，是以，一般情況下是不知道導出程式（exp）的版本、導出時間或者導出模式等資訊的。那麼如何從現有的dmp檔案中擷取到這些資訊呢？下面作者将一一講解。
下面的示例中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
+00:00
BYTE
UNUSED
下面的示例中，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表
下面的示例中，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>
資料泵工具（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
。
exp和imp工具中可能存在把table從一個庫exp然後imp到另一個資料庫出現沒有指定tablespace而無法imp，imp的indexfile參數中可以解決的。
Oracle的imp工具指定indexfile參數後，可以不導入任何對象，而隻把需要建立的index以sql語句的形式寫入文本檔案。建立庫表等sql語句也會寫入，但用rem注釋屏蔽。
一、檢視并修改導入對象的存儲參數
如果原始庫中有些表比較大，exp導出對象的初始存儲空間設定可能比較高，導入時需要先申請配置設定較大的存儲空間，如果隻進行邏輯結構的遷移耗時較長。這時可以用indexfile參數導出sql語句，篩選出初始空間較高的建表語句，手工建立。再次導入時使用ignore選項忽略對象建立錯誤。
如何解析inexfile檔案：可以考慮用sed編輯器進行正規表達式替換，也可以寫個程式解析出initial超出一定門檻值的庫表及其sql。
--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
-------------------------------------------------------------------------
一個網友找到我說，一個dmp檔案導入資料庫中，中文一直是亂碼，看我能否幫忙解決一下。說真心話，一般情況下，亂碼問題和安裝問題，我一般不想接手，因為可能很簡單的問題，有的人懶的動腦，碰到問題就問。尤其對于安裝類問題，照着安裝文檔，一步一步來，一般都沒有問題。在這裡把一張網友分享的圖檔再分享一下：
可是，問字元集的的哥們，我能感覺到他自己是下了功夫的，都是自己摸索了，實在解決不了，才找到的我。這種情況下，我果斷是要幫助的。好了，廢話不多說了，且看整個處理過程吧。
項目
source db
target db
db 類型
db version
10.2.0.1.0
db 存儲
OS版本及kernel版本
字元集
US7ASCII
GBK
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
+08:00
[oracle@rhel6lhr ~]$ strings /tmp/hhris.dmp | grep "CREATE TABLE"|awk '{print $3}'|sed 's/"//g'
ADDTOHIS
APPOINT
APPOINTDETAIL
APPOINTMASTER
BACKUP_HISPPOINT
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'))
------------------------------------------------------------------------------------------------------------------------
可以得出以下結論：
1、dmp檔案是由10.02.01的用戶端導出的
2、基于HHRIS使用者導出
3、該使用者下有很多表
4、dmp檔案的字元集是US7ASCII
主要檢視是否有其它表空間導緻不能導入的問題。
[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。
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
. 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]$
可以成功導入，但是查詢的時候，有中文亂碼。
使用UE或Pilotedit軟體，以16進制的格式打開dmp檔案，修改dmp檔案的第4行的第1-4個位元組。
修改前：
修改後：
其實，也有資料顯示需要把第一行的第2和第3位元組，第4行的第1-4位元組全部修改掉，如下所示：
經過小麥苗的測試，發現這3個地方全部修改掉，也可以成功導入。
修改後儲存檔案，上傳伺服器，重新導入，導入後查詢，發現中文已經可以正常顯示了。
還有一種不顯示亂碼的方式，那就是将US7ASCII字元集的dmp檔案導入到US7ASCII字元集的資料庫中。
-characterset US7ASCII -nationalCharacterSet AL16UTF16 \
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
導入後，在Windows上設定用戶端環境變量NLS_LANG為AMERICAN_AMERICA.US7ASCII，然後重新開機PL/SQL DEVELOPER軟體後就可以正常顯示中文了。
本來想着，這樣再采用GBK的字元集導出，然後導入GBK的資料庫中，結果發現這種方法行不通，始終有亂碼。其實，走到這一步，還可以将資料導出成文本格式的檔案，然後将文本格式的檔案再導入GBK字元集的資料庫中仍然是可行的。
有種辦法處理将US7ASCII字元集的dmp檔案導入到ZHS16GBK字元集的資料庫中的中文亂碼問題。第一，修改dmp檔案中代表字元集的字元。第二，導入US7ASCII字元集的庫中，然後導出成文本格式，再導入到GBK的庫中。
本文轉自lhrbest 51CTO部落格，原文連結：http://blog.51cto.com/lhrbest/1923865，如需轉載請自行聯系原作者