快速解決IMP中的ORA-00959

標籤：oracle   imp   blob   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欄位的表上。

原文地址

本文出自 “馬鈴薯馬鈴薯，我是地瓜” 部落格，請務必保留此出處http://254698001.blog.51cto.com/2521548/1703281

快速解決IMP中的ORA-00959