【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

+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

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.

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

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

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

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