oracle12c导入dmp不同表空间,exp_imp_迁移_同用户_不同表空间的小记

概述

1，源系统:10.2.0.4 rhel5 for x86

目标系统：10.2.0.4 rhel5 for x86

2,源与目标系统的字符集及国家字符集一致，皆为utf8与alutf16

3,源系统：exp 用户及表空间为pdtong及tongyihua

目标系统：imp用户及表空间为pd及pd

实验：如果直接imp导入

imp userid=system/system fromuser=pd touser=pd file=20100715_export_pd.dmp log=new_new_import.log

报错如下：

-bash-3.1$ more 1141_imp.log

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

import done in UTF8 character set and AL16UTF16 NCHAR character set

. importing PD's objects into PD

. . importing table                   "APPCOLUMNS"       1311 rows imported

. . importing table                    "APPTABLES"        126 rows imported

IMP-00017: following statement failed with ORACLE error 959:

"CREATE TABLE "C_ATTACH_INFO" ("ATTACH_TID" CHAR(16) NOT NULL ENABLE, "ATTAC"

"H_NAME" VARCHAR2(256), "ATTACH_CODE" VARCHAR2(128), "IS_TEXT" CHAR(1), "TEX"

"T_DETAIL" CLOB, "BINARY_DETAIL" BLOB, "FILENAME" VARCHAR2(256), "EXTNAME" V"

"ARCHAR2(256), "FILE_SIZE" NUMBER(10, 0), "UI_SIZE" VARCHAR2(256), "DURATION"

"" NUMBER(10, 0), "CREATOR_ID" CHAR(16), "CREATE_TIME" DATE, "MEMO" VARCHAR2"

"(3000))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 6553"

"6 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TONGYIHUA""

" LOGGING NOCOMPRESS LOB ("TEXT_DETAIL") STORE AS  (TABLESPACE "TONGYIHUA" E"

"NABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING  STORAGE(INITIAL "

"65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) LOB ("BINARY_DETA"

"IL") STORE AS  (TABLESPACE "TONGYIHUA" ENABLE STORAGE IN ROW CHUNK 8192 RET"

"ENTION NOCACHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1"

" BUFFER_POOL DEFAULT))"

IMP-00003: ORACLE error 959 encountered

ORA-00959: tablespace 'TONGYIHUA' does not exist

. . importing table               "C_BOOK_VERSION"         52 rows imported

. . importing table             "C_BOOK_VERSION_1"          0 rows imported

. . importing table               "C_CHAPTER_INFO"       3929 rows imported

. . importing table                "C_COUNTRYINFO"         85 rows imported

. . importing table          "C_DICTIONARYCATALOG"         34 rows imported

. . importing table             "C_DICTIONARYINFO"        102 rows imported

. . importing table              "C_KNOWLEDGEINFO"          0 rows imported

. . importing table       "C_KNOWLEDGETYPEMAPPING"          0 rows imported

. . importing table             "C_KNOWLEDGE_INFO"         99 rows imported

IMP-00017: following statement failed with ORACLE error 959:

"CREATE TABLE "C_LOGINFO" ("LOGID" CHAR(16) NOT NULL ENABLE, "LOGTYPEID" CHA"

"R(16), "SEVERITY" CHAR(16), "MESSAGEINFO" VARCHAR2(1024), "EXCEPTIONINFO" C"

"LOB, "IPADDRESS" VARCHAR2(128), "CUSTOMERID" CHAR(16), "ISSYSTEMUSER" CHAR("

"1), "PAGEURL" VARCHAR2(256), "CREATEDTIME" DATE, "MEMO" VARCHAR2(3000))  PC"

"TFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS"

" 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TONGYIHUA" LOGGING NO"

"COMPRESS LOB ("EXCEPTIONINFO") STORE AS  (TABLESPACE "TONGYIHUA" ENABLE STO"

"RAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING  STORAGE(INITIAL 65536 FRE"

"ELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"

IMP-00003: ORACLE error 959 encountered

很明显，导出与导入的表空间不一致，最终imp可以成功，但会报一堆的错，当然错误基本是一样的，表空间不存在及表视图不一存在.

有 两种方法：

1，在imp前，直接用vi批量替换dmp文件的表空间名字为要导入的表空间名字

vi 20100715_export_pd.dmp

:%s/TONGYIHUA/PD/g

:wq或:x --保存退出

执行导入imp userid=system/system fromuser=pd touser=pd file=20100715_export_pd.dmp log=new_new_import.log

2,在imp前，在目标数据库创建与dmp文件一致的表空间

执行导入imp userid=system/system fromuser=pd touser=pd file=20100715_export_pd.dmp log=new_new_import.log

具体选用哪种，视当时的应用情景