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

原文地址