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

原文地址