I encountered an issue while importing a oracle dump, and a nice workaround.
The situation:
We have two oracle users, each one with a specific tablespace (ORI_TSDATA for user ORI on ORI_SID, TARGET_TSDATA for user TARGET on TARGET_SID).
I want to export a dump of tables and data from user1 and import it inside user2 tablespace.
The export is made with the exp command (details here).
exp USERID=ORI/ORI_PASSWORD@ORI_SID File=ori.dmp LOG=export.log |
The import is performed with
imp USERID=TARGET/TARGET_PASSWORD@TARGET_SID File=ori.dmp LOG=import.log FROMUSER=ORI TOUSER=TARGET |
The issue:
Although we us the FROMUSER and TOUSER parameters in the import, the import utility is unable to migrate some tables to a different tablespace.
The migration works for tables using standard columns types, but not with the ones that contain LOB data (Binary objects).
In fact, the import utility tries to recreate the tables that contain BLOB in ORI_TSDATA (the exported tablespace) that does not exist in the target system.
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "BINARY_CONTENTS" ("ID" NUMBER(19, 0) NOT NULL ENABLE, "CONTEN"
"T" BLOB NOT NULL ENABLE, "MIME_TYPE" VARCHAR2(255 CHAR) NOT NULL ENABLE, "F"
"ILE_NAME" VARCHAR2(255 CHAR) NOT NULL ENABLE, "FILE_SIZE" NUMBER(10, 0) NOT"
" NULL ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIA"
"L 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ORI"
"_TSDATA" LOGGING NOCOMPRESS LOB ("CONTENT") STORE AS (TABLESPACE "ORI_"
"TSDATA" ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSION 10 NOCACHE LOGGING ST"
"ORAGE(INITIAL 81920 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'ORI_TSDATA' does not exist
In fact, if you visualize the dump file, you will notice that LOB create statements explicitly mention the target Tablespace (ie the one you exported from) while other create statement do not mention them.
The official fix:
We should create a tablespace similar to the exported one (ORI_TSDATA) in the target server and migrate the tables to the target tablespace (TARGET_TSDATA) after the import. This cannot always be done, for example if ORI_TSDATA is already used for another purpose on the target server.
The workaround:
We can force the import to TARGET_TSDATA:
- create the table structure in TARGET_TSDATA prior to importing the dump
- use the ignore=yes parameter at import time
The import utility will ignore the warnings, and use the existing tables in TARGET_TSDATA to populate the data.
Use the ddl_create_tables.sql script if you have one or retrieve thetable creation orders from the dump otherwise.
imp TARGET_PASSWORD@TARGET_SID File=ori.dmp LOG=import.log FROMUSER=ORI TOUSER=TARGET IGNORE=y |
Another workaround
We could edit the dump file with an editor like VI and replace the target tablespace names inside.