Friday, December 4, 2015

ORA-00959: tablespace 'XXX' does not exist - imp or impdp


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing XXX_2016's objects into XXX_2016
. . importing table                "AAA"       4231 rows imported
. . importing table           "BBB"        429 rows imported
. . importing table            "CCC"         10 rows imported
. . importing table               "DDD"        504 rows imported
. . importing table           "EEE"      12053 rows imported
. . importing table     "FFF"        443 rows imported
. . importing table               "FFF1"        504 rows imported
. . importing table          "FFF2"      23544 rows imported
. . importing table            "FFF3"          1 rows imported
. . importing table            "FFF4"      10984 rows imported
. . importing table             "FFF5"     382879 rows imported
. . importing table                 "FFF6"         12 rows imported
. . importing table             "FFF7"        423 rows imported
. . importing table                     "FFF8"     382879 rows imported
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE TABLE "XXX" ("REQUEST_ID" VARCHAR2(20) NOT NULL ENABLE, "CR_ID" NUMBER NOT NULL ENABLE, "GLOBAL__ID" VARCHAR2(50), "CREATION_S"
 "TATUS" CLOB)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL"
 " 786432 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL"
 " DEFAULT) TABLESPACE "TEST_TBL" LOGGING NOCOMPRESS LOB ("CREATION_STATU"
 "S") STORE AS BASICFILE  (TABLESPACE "TEST_TBL" ENABLE STORAGE IN ROW CH"
 "UNK 8192 RETENTION  NOCACHE LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MIN"
 "EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TEST_TBL' does not exist
. . importing table               "FFF9"      10787 rows imported
. . importing table            "FFF10"     653765 rows imported
. . importing table                   "FFF11"       2826 rows imported
. . importing table                   "FFF12"       3171 rows imported
. . importing table                   "FFF13"       3268 rows imported
. . importing table                   "FFF14"       1888 rows imported
. . importing table                   "FFF15"       1139 rows imported
. . importing table                   "FFF16"       3530 rows imported
. . importing table              "FFF17"         91 rows imported
. . importing table                    "USAGE"        360 rows imported
IMP-00033: Warning: Table "SAMPLE_1" not found in export file
IMP-00033: Warning: Table "SAMPLE_2" not found in export file
IMP-00033: Warning: Table "SAMPLE_4" not found in export file
IMP-00033: Warning: Table "SAMPLE_5" not found in export file
Import terminated successfully with warnings.

Solution:
impdp or imp will return a ORA-00959 when a table definition specifies multiple tablespaces (i.e. a CLOB column stored in a separate tablespace.  In these cases, the solution is to pre-create the table (punching the DDL with dbms_metadata) and use impdp or imp with ignore=y.

No comments: