How to import a dump for a deleted tablespace

dumporacletablespaces

I'm trying to import a database dump, however I am encountering this error:

ORA-39083: Object type TABLE:"MYSCHEMA"."MINF" failed to create with error:
ORA-00959: tablespace '_$deleted$5$0' does not exist
Failing sql is:
CREATE TABLE "MYSCHEMA"."MINF" (
    "P_MID" VARCHAR2(16 BYTE) NOT NULL ENABLE
    , "XML_ORIG_MSG" "SYS"."XMLTYPE"
    , "XML_MSG" "SYS"."XMLTYPE"
    , "P_OFFICE" CHAR(3 BYTE) NOT NULL ENABLE
    , "P_DEPARTMENT" CHAR(3 BYTE) DEFAULT '...'
    , "P_MSG_TYPE" VARCHAR2(15 BYTE) DEFAULT '...'
    , "P_MSG_SUB_TYPE" VARCHAR2(10 BYTE)
    , "P_CDT_MOP" VARCHAR2(6 BYTE)
    , "P_PREFERRED_CDT_MOP" VARCHAR2(6

It's seems the tablespace for the MINF table has been dropped before getting the dump.

Is there a workaround to still import this table?

Update:

I have tried impdp statement, with the same result: 

./impdp system/****** 
DIRECTORY=GPPDUMP 
DUMPFILE=SPOCBC_SCD02.ROCKY.20120919102739.dmp.1
,SPOCBC_SCD02.ROCKY.20120919102739.dmp.2
,SPOCBC_SCD02.ROCKY.20120919102739.dmp.3
,SPOCBC_SCD02.ROCKY.20120919102739.dmp.4 
LOGFILE=baru.log 
REMAP_SCHEMA=SPOCBC_SCD02:OCBCGPP5 
REMAP_TABLESPACE=_$deleted$5$0:P_DATA

Best Answer

There is a good chance that the default (and/or temporary) tablespace for MYSCHEMA was dropped. If that is the case, give MYSCHEMA a new tablespace, with quota and re-run.

This can be checked by viewing the entries in dba_users.

This is a bug and should normally not occur.

Related Question