IMP-00003 ORA-00959 Errors While Importing Data Into Existing Table Of 11gR2 Using Traditional Import (Doc ID 1180873.1)

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

Export Testcase:

$ exp system/manager file=x.dmp log=x.log tables=P316109.BIOMETRIC
Export: Release 11.2.0.1.0 - Production on Tue Aug 17 10:18:43 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and UTF8 NCHAR character set server uses WE8ISO8859P15 character set (possible charset conversion)
About to export specified tables via Conventional Path ... Current user changed to P316109

EXP-00011: P316109.BIOMETRIC does not exist Export terminated successfully with warnings.

SQL> conn P316109/P316109 Connected. SQL> alter table BIOMETRIC move;
Table altered.

$ exp system/manager file=x1.dmp log=x1.log tables=P316109.BIOMETRIC
Export: Release 11.2.0.1.0 - Production on Tue Aug 17 10:20:10 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and UTF8 NCHAR character set server uses WE8ISO8859P15 character set (possible charset conversion)
About to export specified tables via Conventional Path ... Current user changed to P316109 . . exporting table BIOMETRIC Export terminated successfully without warnings

Import Testcase:

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace NRSDB_EXT datafile '/tmp/NRSDB_EXT.dbf' size 10m;
Tablespace created.

SQL> create user p316109 identified by p316109;
User created.

SQL> grant connect,resource to p316109;
Grant succeeded.

SQL> alter user p316109 quota unlimited on NRSDB_EXT;
User altered.

SQL> select USERNAME,TABLESPACE_NAME,BYTES,max_bytes from dba_ts_quotas where username='P316109'
 2  ;

USERNAME                       TABLESPACE_NAME                     BYTES    MAX_BYTES
------------------------------ ------------------------------ ----------    ----------
P316109                        NRSDB_EXT                               0          -1

SQL> connect p316109/p316109
Connected.

Now create the Table in a different tablespace For example USERS..

SQL> CREATE TABLE "P316109"."BIOMETRIC"
 2       (    "BIOMETRIC_ID" NUMBER(25,0) NOT NULL ENABLE,
 3            "CIVIL_NUMBER" NUMBER(14,0) NOT NULL ENABLE,
 4            "HIRES_PHOTO" BLOB,
 5            "CAPTURE_DATE" DATE
 6       ) SEGMENT CREATION DEFERRED
 7      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 8      STORAGE( INITIAL 243269632 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS"
 9     LOB ("HIRES_PHOTO") STORE AS BASICFILE (
10     TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING
11     STORAGE( INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
12    ;

Table created.

$ imp system/manager  file=nrsext.dmp log=nrsext-imp.log fromuser=nrsext touser=p316109 tables=BIOMETRIC ignore=y
Import: Release 11.2.0.1.0 - Production on Thu Aug 19 12:59:30 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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 SYS, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export client uses AR8MSWIN1256 character set (possible charset conversion)
. importing NRSEXT's objects into P316109
Import terminated successfully without warnings.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop tablespace nrsdb_ext including contents and datafiles;
Tablespace dropped.

SQL> conn p316109/p316109
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIOMETRIC                      TABLE

$ imp system/manager  file=nrsext.dmp log=nrsext-imp.log fromuser=nrsext touser=p316109 tables=BIOMETRIC ignore=y
Import: Release 11.2.0.1.0 - Production on Thu Aug 19 13:00:42 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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 SYS, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export client uses AR8MSWIN1256 character set (possible charset conversion)
. importing NRSEXT's objects into P316109
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "BIOMETRIC" ("BIOMETRIC_ID" NUMBER(25, 0) NOT NULL ENABLE, "CI"
"VIL_NUMBER" NUMBER(14, 0) NOT NULL ENABLE, "HIRES_PHOTO" BLOB, "CAPTURE_DAT"
"E" DATE)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 243"
"269632 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "NRSDB"
"_EXT" LOGGING NOCOMPRESS LOB ("HIRES_PHOTO") STORE AS  (TABLESPACE "NRSDB_E"
"XT" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING  STORAGE"
"(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'NRSDB_EXT' does not exist    <<<<<<<------------------------See this error.
Import terminated successfully with warnings.

$ sqlplus p316109/p316109

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 19 13:01:45 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter table "BIOMETRIC" move;    ----------------------------------------------->>>> Table moved
Table altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dbgrid1 ~]$ imp system/manager  file=nrsext.dmp log=nrsext-imp.log fromuser=nrsext touser=p316109 tables=BIOMETRIC ignore=y

Import: Release 11.2.0.1.0 - Production on Thu Aug 19 13:02:23 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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 SYS, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export client uses AR8MSWIN1256 character set (possible charset conversion)
. importing NRSEXT's objects into P316109
Import terminated successfully without warnings.