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

 

具體選用哪種，視當時的應用情景