Tables Spanning Multiple Tablespace

scott@10GR2> create tablespace exp_test
  2  datafile 'e:\oracle\product\10.1.0\oradata\iradsdb\exp_test.dbf'
  3  size 1m
  4  extent management local
  5  uniform size 64k;

Tablespace created.

scott@10GR2> alter user scott default tablespace exp_test;

User altered.

scott@10GR2> create table t (x number);

Table created.

scott@10GR2> create table t2
  2  (
  3     x number,
  4     y number
  5  )partition by range(x)
  6  (
  7     partition p1 values less than (2),
  8     partition p2 values less than (3),
  9     partition p3 values less than (4),
 10     partition p4 values less than (5),
 11     partition p_max values less than (maxvalue)
 12  );

Table created.

So, we start with creating a Tablespace and making this our default tablespace. we then created a simple table and a partitioned table. we now export this schema.

D:\>exp userid=scott/tiger@10GR2 file=d:\scott_exp.dmp tables=(t,t2)

Export: Release 10.2.0.3.0 - Production on Sat Mar 6 00:04:40 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T          0 rows exported
. . exporting table                             T2
. . exporting partition                             P1          0 rows exported
. . exporting partition                             P2          0 rows exported
. . exporting partition                             P3          0 rows exported
. . exporting partition                             P4          0 rows exported
. . exporting partition                          P_MAX          0 rows exported
Export terminated successfully without warnings.

And dropped that tablespace.

scott@10GR2> drop tablespace exp_test including contents;

Tablespace dropped.

scott@10GR2> alter user scott default tablespace Tools;

User altered.

When we imported that, most of the tables doesn't come back.

D:\>imp userid=scott/tiger@10GR2 file=d:\scott_exp.dmp log=d:\imp.txt fromuser=scott touser=scott

Import: Release 10.2.0.3.0 - Production on Sat Mar 6 00:08:39 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table                            "T"          0 rows imported
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE TABLE "T2" ("X" NUMBER, "Y" NUMBER)  PCTFREE 10 PCTUSED 40 INITRANS "
 "1 MAXTRANS 255 TABLESPACE "EXP_TEST" LOGGING PARTITION BY RANGE ("X" )  (PA"
 "RTITION "P1" VALUES LESS THAN (2)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"
 "S 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAU"
 "LT) TABLESPACE "EXP_TEST" LOGGING NOCOMPRESS, PARTITION "P2" VALUES LESS TH"
 "AN (3)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536"
 " FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXP_TEST" L"
 "OGGING NOCOMPRESS, PARTITION "P3" VALUES LESS THAN (4)  PCTFREE 10 PCTUSED "
 "40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUP"
 "S 1 BUFFER_POOL DEFAULT) TABLESPACE "EXP_TEST" LOGGING NOCOMPRESS, PARTITIO"
 "N "P4" VALUES LESS THAN (5)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 "
 "STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TA"
 "BLESPACE "EXP_TEST" LOGGING NOCOMPRESS, PARTITION "P_MAX" VALUES LESS THAN "
 "(MAXVALUE)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 6"
 "5536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXP_TES"
 "T" LOGGING NOCOMPRESS )"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'EXP_TEST' does not exist
Import terminated successfully with warnings.


The only table that came back is our Simple 'Normal' table. For this table, IMP rewrote the SQL. It blanked out the first TABLESPACE EXP_TEST that it came across and retried the CREATE. This rewritten CREATE succeeded. The other CREATE commands, when similarly rewritten, did not succeed. The only solution to this is to create the tables beforehand (using INDEXFILE=Y ) and then import with IGNORE=Y.