概述
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
具体选用哪种,视当时的应用情景
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-668308/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-668308/