3

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 in 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

Hasan Fathi
  • 5,610
  • 4
  • 42
  • 60
Maddy
  • 3,726
  • 9
  • 41
  • 55
  • I don't think you are using [transportable tablespaces](http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces013.htm) - they don't solve your naming problem; and as the docs note you can't use `imp` with them in 11g anyway. – Alex Poole Jun 05 '12 at 11:15

2 Answers2

8

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • 1
    I don't think transportable tablespaces help you. Unless you can use datapump I'm pretty sure manually building the table before the import is your only option. – Alex Poole Jun 05 '12 at 11:17
3

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

Rene
  • 10,391
  • 5
  • 33
  • 46
  • i cant create another table space here, instead i am searching for a way where i can map MP5TEXT to existing tablespace – Maddy Jun 05 '12 at 08:55