oracle import error

Question

While importing a dump file into oracle 11gr2 i am getting this error please suggest a way to resolve this

*Warning: the objects were exported by D7IDMP, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses WE8ISO8859P1 character set (possible charset conversion)
. importing D7IDMP's objects into MIGRATE
. . importing table                    "TXALLTXTS"     245628 rows imported
. . importing table                       "TXAUTH"        377 rows imported
. . importing table                    "TXBAIMERT"        244 rows imported
. . importing table                     "TXBARESC"        178 rows imported
. . importing table                     "TXBOILER"     458940 rows imported
. . importing table                    "TXCLASSES"        980 rows imported
. . importing table              "TXCLOSEDPERIODS"          1 rows imported
. . importing table                  "TXCOMPANIES"          1 rows imported
. . importing table                 "TXCONCLAUSES"        187 rows imported
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE TABLE "TXCONFIGSETTINGS" ("CFS_CODE" VARCHAR2(20), "CFS_RCODE" VARCHAR2(2"
 "0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 16384 FR"
 "EELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "MP5TEXT" LOGGI"
 "NG NOCOMPRESS LOB ("CFS_XMLCONFIG") STORE AS  (TABLESPACE "MP5TEXT" ENABLE "
 "STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE  STORAGE(INITIAL 65536 FREE"
 "LISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'MP5TEXT' does not exist*

As per alex comments i tried with other attributes of imp but it resulted someother error

The command which i run is

imp fromuser=d7idmp touser=&&from_user file=test.dmp log= test.log ignore=y transport_tablespace = y tablespaces = MP5TEXT

the error is

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set export client uses WE8ISO8859P1 character set (possible charset conversion) IMP-00053: Import mode incompatible with Export dump file IMP-00000: Import terminated unsuccessfully

Answer 1

If you are able to use datapump instead of the old imp/exp tools, you can remap tablespaces (and other things) on the fly.

If not, the usual way to deal with this is to manually create the table in the new tablespace, and then run the imp with ignore=y so it doesn't complain that the object exists.

You can get the DDL for the table - with the old tablespace - by running imp with indexfile=<file>. This will give you a text file with the DDL for the tables and indexes, but with the table commands commented out with rem. You can extract the bits you need and edit them as required for the new database.

If you haven't already, look at the import parameters in the documentation.

Answer 2

You need to create tablespace MP5TEXT. The import utility needs the same tablespaces that existed in the source database to import the tables.