【exp/imp】將US7ASCII字符集的dmp檔案匯入到ZHS16GBK字符集的資料庫中
【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群，學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2138791/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 修改exp 的 匯出dmp檔案的字符集
- 查詢dmp檔案(exp方式匯出) 字符集
- 字符集為ZHS16GBK的資料庫匯入到字符集為AL32UTF8的資料庫資料庫
- ORACLE11G中us7ascii 字符集匯出檔案匯入zhs16gbk 庫中亂碼問題處理OracleASCII
- EXP/IMP 中涉及到的字符集的設定
- 用exp/imp把遠端資料匯入到本地資料庫中資料庫
- 雙位元組中文字符集匯出的DMP檔案匯入UTF8字符集
- 識別exp匯出檔案所用的字符集
- 從Export DMP檔案看匯出字符集（上）Export
- 從Export DMP檔案看匯出字符集（下）Export
- imp/exp 字符集轉換
- oracle資料的匯入匯出imp/expOracle
- 資料匯入匯出EXP/IMP
- exp/imp匯出匯入資料
- Oracle11g資料庫exp產生的dmp檔案,進行imp匯入時,ORA-01400的處理Oracle資料庫
- oracle資料匯出匯入（exp/imp）Oracle
- 檢視較小dmp檔案，匯出時客戶端的字符集客戶端
- US7ASCII字符集轉換為ZHS16GBKASCII
- 探索Oracle之 EXP/IMP過程中的字符集問題Oracle
- exp匯出檔案時 字符集設定問題
- Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令Oracle資料庫
- Oracle資料匯入匯出imp/exp命令Oracle
- 把csv檔案的資料匯入到oracle資料庫中Oracle資料庫
- Windows DOS窗體下Oracle 資料庫的匯入匯出(IMP/EXP)命令WindowsOracle資料庫
- Oracle資料匯入匯出imp/exp命令(轉)Oracle
- Oracle exp/imp字符集相關問題Oracle
- 如何根據exp匯出檔案確定client端匯出時的字符集client
- 使用Oracle 的 imp ,exp 命令實現資料的匯入匯出Oracle
- 在不同字符集的資料庫之間匯入資料的方法(轉)資料庫
- 【EXP/IMP】使用EXP /IMP工具“模糊”匯出和匯入
- Oracle中exp,imp(匯入匯出)資料遷移注意事項Oracle
- exp/imp匯出匯入工具的使用
- oracle 9i的資料庫的dmp匯入到10G資料庫Oracle資料庫
- oracle匯入dmp檔案Oracle
- Oracle exp/imp匯出匯入工具的使用Oracle
- EXP直接匯出壓縮問津，IMP直接匯入壓縮檔案的方法
- plsql Oracle匯入dmp檔案SQLOracle
- imp 11g的exp檔案到10g庫（IMP-00010）