【exp/imp】将US7ASCII字符集的dmp文件导入到ZHS16GBK字符集的数据库中

【exp/imp】将US7ASCII字符集的dmp文件导入到ZHS16GBK字符集的数据库中

 

1.1  BLOG文档结构图

wpsA21C.tmp 

 

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的文件。示例如下：

wpsA22D.tmp 

需要注意的是，十六进制在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>

 

 

wpsA22E.tmp 

 

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文件导入数据库中，中文一直是乱码，看我能否帮忙解决一下。说真心话，一般情况下，乱码问题和安装问题，我一般不想接手，因为可能很简单的问题，有的人懒的动脑，碰到问题就问。尤其对于安装类问题，照着安装文档，一步一步来，一般都没有问题。在这里把一张网友分享的图片再分享一下：

wpsA23F.tmp

可是，问字符集的的哥们，我能感觉到他自己是下了功夫的，都是自己摸索了，实在解决不了，才找到的我。这种情况下，我果断是要帮助的。好了，废话不多说了，且看整个处理过程吧。

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文件：

wpsA24F.tmp 

大约30M，解压后有282M左右：

wpsA250.tmp 

[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个字节。

修改前：

wpsA251.tmp 

修改后：

wpsA252.tmp 

 

其实，也有资料显示需要把第一行的第2和第3字节，第4行的第1-4字节全部修改掉，如下所示：

wpsA253.tmp 

经过小麦苗的测试，发现这3个地方全部修改掉，也可以成功导入。

 

修改后保存文件，上传服务器，重新导入，导入后查询，发现中文已经可以正常显示了。

wpsA264.tmp 

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群，学习最实用的数据库技术。


DBA笔试面试讲解
 欢迎与我联系


posted @ 2017-05-09 21:12  DB宝  阅读(10347)  评论(0)  编辑  收藏  举报