如何imp导入table表中含有lob列分属不同表空间tablespace

 自一期生产迁移某个表，出错如下：

IMP-00017: 由于 ORACLE 错误 959, 以下语句失败:
 "CREATE TABLE "PORTAL_TERM_SPEC" ("ID" VARCHAR2(32) NOT NULL ENABLE, "CODE" "
 "VARCHAR2(32), "MKT_RES_CD" VARCHAR2(32), "MKT_RES_NAME" VARCHAR2(500), "MKT"
 "_RES_TYPE_ID" VARCHAR2(20), "TERM_MANF" VARCHAR2(100), "TERM_MODEL" VARCHAR"
 "2(200), "TERM_COLOR" VARCHAR2(64), "LONG_TITLE" VARCHAR2(2000), "MEDIUM_TIT"
 "LE" VARCHAR2(2000), "SHORT_TITLE" VARCHAR2(2000), "SECOND_TITLE" VARCHAR2(2"
 "000), "SHORT_DESC" VARCHAR2(3024), "REFERENCE_PRICE" VARCHAR2(20), "SALES_P"
 "RICE" VARCHAR2(20), "RECOMMEND" VARCHAR2(20), "IS_UP_WAP" VARCHAR2(20), "IS"
 "_UP_WEB" VARCHAR2(20), "IS_SHOW_WAP" VARCHAR2(20), "IS_SHOW_WEB" VARCHAR2(2"
 "0), "SEO_TITLE" VARCHAR2(500), "SEO_KEYWORDS" VARCHAR2(500), "SEO_DESCRIPTI"
 "ON" VARCHAR2(500), "EXPERIENCE_3D_URL" VARCHAR2(512), "SPECIAL_AREA_URL" VA"
 "RCHAR2(512), "JAVA_3D_URL" VARCHAR2(512), "LISTING_DATE" VARCHAR2(14), "WAP"
 "_IMG1" VARCHAR2(100), "WAP_IMG2" VARCHAR2(100), "WAP_IMG3" VARCHAR2(100), ""
 "WAP_IMG4" VARCHAR2(100), "WAP_IMG5" VARCHAR2(100), "IMG_FILE_NAME_PREFIX" V"
 "ARCHAR2(512), "LIST_IMG2" VARCHAR2(512), "LIST_IMG3" VARCHAR2(512), "LIST_I"
 "MG4" VARCHAR2(512), "LIST_IMG5" VARCHAR2(512), "LIST_IMG6" VARCHAR2(512), ""
 "SPEC" VARCHAR2(100), "DESCRIBE" CLOB, "PACKING_LIST" VARCHAR2(4000), "AFTER"
 "_SERVICE" VARCHAR2(4000), "VIDEO" VARCHAR2(512), "ALBUM" VARCHAR2(512), "RE"
 "L_TERM" VARCHAR2(20), "GIFT_INFO" VARCHAR2(100), "DISABLE_OPID" VARCHAR2(10"
 "0), "DISABLE_DATE" TIMESTAMP (6), "CREATE_OPID" VARCHAR2(100), "CREATE_DATE"
 "" TIMESTAMP (6), "REC_STATUS" NUMBER(1, 0) NOT NULL ENABLE, "REMARK1" VARCH"
 "AR2(100), "REMARK2" VARCHAR2(100), "REMARK3" VARCHAR2(100), "REMARK4" VARCH"
 "AR2(100), "REMARK5" VARCHAR2(100), "REMARK6" VARCHAR2(100), "REMARK7" VARCH"
 "AR2(100), "REMARK8" VARCHAR2(100), "REMARK9" VARCHAR2(100), "REMARK10" VARC"
 "HAR2(100), "REMARK11" VARCHAR2(100), "REMARK12" VARCHAR2(100), "REMARK13" V"
 "ARCHAR2(100), "REMARK14" VARCHAR2(100), "REMARK15" VARCHAR2(100), "SPEC_PAR"
 "A" CLOB, "IS_UP_ZTKHD" VARCHAR2(2 CHAR), "IS_UP_WIN8" VARCHAR2(2 CHAR), "IS"
 "_UP_ZNDS" VARCHAR2(2 CHAR))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 "
 "STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TA"
 "BLESPACE "TBS_ZXKF" LOGGING NOCOMPRESS LOB ("SPEC_PARA") STORE AS  (TABLESP"
 "ACE "TBS_CT10000" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE  S"
 "TORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) LO"
 "B ("DESCRIBE") STORE AS  (TABLESPACE "SHWT" ENABLE STORAGE IN ROW CHUNK 819"
 "2 PCTVERSION 10 NOCACHE  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS "
 "1 BUFFER_POOL DEFAULT))"
IMP-00003: 遇到 ORACLE 错误 959
ORA-00959: 表空间 'TBS_CT10000' 不存在
成功终止导入, 但出现警告。

 

分析：

　

１，此表含有２个lob列，与所属表各独立于存储于不同的表空间中

 

处理：

１，把上述的导出的dmp先导入到本地库中，依据此表所属的几个表空间，把此表导入到本地库

２，在本地库把此表有如下sql转化到同一个表空间users中

alter table portal_term_spec move tablespace users lob(describe,SPEC_PARA) store as (tablespace users);

３，查看成果

select * from user_lobs where table_name='PORTAL_TERM_SPEC';

select * from user_tables where table_name='PORTAL_TERM_SPEC';

4,把本地库的表导出

5,把第４步产生的dmp导入到开发库即可

　　

　

　　

　　

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-748802/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-748802/