快速解決IMP中的ORA-00959
快速解決IMP中的ORA-00959
用IMP向測試庫導入DMP時，又遇到了ORA-00959表空間不存在的問題。
一般的解決方法：
1.先建表，在導入dmp時加ignore=y 的參數。
2.新建對應的表空間
3.使用expdp從新導出，在impdp時用REMAP_TABLESPACE
無論用那種方法都比較麻煩，都需要耗費一定的工作量。如果在oracle10g中，
采用重命名表空間的方法,可以快速解決這個問題。
SQL> alter tablespace &old_tbsname rename to &new_tbs_name;
如當前用戶默認的表空間為TEST,現在改為需要的表空間YWDBS
SQL> alter tablespace TEST rename to YWDBS;
/oracle$imp TEST/TEST file=dev_bak_20110702.dmp tables=PRPDRISK statistics=none
Import: Release 10.2.0.4.0 - Production on Wed Jul 6 09:39:25 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by DEV, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing DEV's objects into REPORTTEST
. importing DEV's objects into REPORTTEST
IMP-00017: following statement failed with ORACLE error 959:
...
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'YWDBS' does not exist
Import terminated successfully with warnings.
SQL> alter tablespace TEST rename to YWDBS;
重命名表空間後，會在alert.log中看到如下信息：
Tablespace 'TEST' is renamed to 'YWDBS'.
Completed: alter tablespace TEST rename to YWDBS
重命名表空間後，dmp可以順利導入
/oracle$imp TEST/TEST file=dev_bak_20110702.dmp tables=PRPDRISK statistics=none
Import: Release 10.2.0.4.0 - Production on Wed Jul 6 10:05:08 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by DEV, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing DEV's objects into REPORTTEST
. importing DEV's objects into REPORTTEST
. . importing table "PRPDRISK" 68 rows imported
Import terminated successfully without warnings.
為了管理上的規范，在導入成功後可以在將表空間名該回去。
IMP中的ORA-00959一般發生在有CLOB字段的表上。
