ORA-39083 ORA-00959

ORA-39083: Object type TABLE:"TSALE"."B_SELL_CLASSIFY" failed to create with error:

ORA-00959: tablespace 'SALES' does not exist

Failing sql is:

CREATE TABLE "TSALE"."B_SELL_CLASSIFY" ("SELL_CLASS_NO" VARCHAR2(50 BYTE) NOT NULL ENABLE, "SELL_CLASS_NAME" VARCHAR2(100 BYTE), "I

" VARCHAR2(36 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65

36 NEXT 8192 MINEXTENTS 1 MAXEXTENTS  2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL

ORA- 39083: Object type TABLE:"TSALE"."B_TRANS_ADDR" failed to create with error:

ORA- 00959: tablespace 'SALES' does not exist

Failing sql is:

解决 

改名字 

 alter tablespace TEST  rename to YWDBS;

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

创建表，提示ORA- 00959:表空间'MC_DATA'不存在的解决

 

首先，先确认当前用户的表空间。

 

select username,default_tablespace,

temporary_tablespace from dba_users where username='MCMIDYL'; 

   www.2cto.com  

发现默认表空间是'MC_DATA'，但是MC_DATA'已经删除。

 

只需要将默认表空间改为，用户中存在的表空间即可。

 

修改语句如下

 

ALTER USER 用户名 DEFAULT TABLESPACE 表空间名

 

 参考：

 

一次艰辛的数据导入

一、导出文件

[sql]  view plain copy

1. SELECT CURRENT_SCN FROM V$DATABASE;  
2. expdp system/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=FALABELLA_AR_20111214.DMP SCHEMAS=FALABELLA_AR_CORE,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_PUB logfile=FALABELLA_AR_expdp_20111214.log status=10 parallel=1 CONTENT=ALL flashback_scn=612654934  

二、压缩dmp文件

[sql]  view plain copy

1. gzip -9 FALABELLA_AR_20111214.DMP