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)
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)! ;-)
2 comments:
This is also true for blobs (of course) and partitions ;-)
Actually anything which can have a second storage clause in a lower lexical level ...
I am also facing the same problem while exporting and importing data from development to production database
Post a Comment