Oracle Export / Import problem with CLOB's

I’m currently reorganising some databases…
As I do it for the x time, I’m working on “auto-pilot”.
But suddenly I received an error:
--
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TOOLS_DATA' does not exist
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "D_JOB" ("O_ID" NUMBER(10, 0), "D_TITLE" VARCHAR2(250), "D_OUTPUT" CLOB, "D_PERCENTAGE" NUMBER(10, 0), "D_LASTUPDATE" NUMBER(10, 0), "D_OWNER" NUMBER(10, 0), "D_STATUS" VARCHAR2(20), "D_PRIORITY" NUMBER(10, 0), "D_TYPE" VARCHAR2(20), "D_TIME" VARCHAR2(5), "D_DATE" DATE, "D_CLASS" VARCHAR2(250)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TOOLS_DATA" LOB ("D_OUTPUT") STORE AS (TABLESPACE "TOOLS_DATA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE STORAGE(INITIAL 32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
--

What I did:

• create a full export of the source database
• create all the necessary users, roles and tablespaces on the target database
  (use of naming conventions, from dictionary to locally managed tablespace, change of parameters etc.)
• import from -> to user on the target database
  (Actually I'm doing the import twice, as the database characterset changed. I create first the structure without data, afterwards change the tables char columns from byte to char and finish with the import of the data)
  

Almost all tables were imported fine, but the tables with some CLOB’s in weren’t!
As I changed the default tablespace of the user (and the old tablespace didn’t exist anymore) the import couldn’t find the tablespace and gave the above error.

I suppose I forgot that if you’ve tables with clobs in, the default tablespace of the user isn’t automatically taken… to solve the problem, I had to recreate the tables manually with the right storage clause (tablespace).

I learn a new thing every day (although I think for this, I just forgot this behaviour of Oracle)! ;-)