Import dmp tablespace name must fit the old?

Question

The Oracle new tablespace name must fit with the old tablespace name?

For example:

The dump file tablespace name is A,and i create a new tablespace B,and it could import table, but has many error?

         ORA-00959:tablespace 'ECASYS'(old) not exits.

This is my import statement:

imp userid='ZHPE/zhpe@ORCL' file='E:\xxxx\xxxx2013-08-15Bak\130815.dmp' log='D:\app\Administrator\oradata\orcl\ZHPE.log' full=y ignore=y;

Is the new tablespace must must must fit the old one??? help!

Answer 1

take a look at the REMAP_TABLESPACE import parameter e.g

REMAP_TABLESPACE=A:B

Answer 2

If you're forced to use the legacy exp and imp tools then the tablespace cannot be changed during the import itself using command-line options. If you can, switch to using the datapump versions, expdp and impdp, and then follow @schurik's advice.

If you can't do that then you'll need a workaround, which is to create the schema objects manually first.

If you run imp with the indexfile option then it will create a file containing the DDL for the tables and indexes:

imp user/password indexfile=schema.sql file=...

The table creation DDL is commented out with REM markers, which you need to remove. You can then edit it to change the tablespace and any other storage options that are no longer appropriate. Then run that schema creation SQL to create all the tables as empty.

Then run the normal import again, but with the ignore=y flag so it doesn't complain (much) that the tables now already exist. The data will still be inserted into those existing tables.

This will be a bit slower if you create the indexes as well as the tables beforehand; normally it would create the tables, insert the data, and then build the indexes, which is more efficient. If the slowdown is significant then you can split your schema.sql into separate table and index creation files and do the same thing manually - create the tables, run the import with ignore=y and indexes=n (to stop it trying and failing to create them), and then create the indexes yourself afterwards.

Clearly this is a bit of a pain, and one of many reasons that switching to datapump is a good idea.