快速解决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字段的表上。