0

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!

Dolphin
  • 29,069
  • 61
  • 260
  • 539

2 Answers2

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
0

take a look at the REMAP_TABLESPACE import parameter e.g 

REMAP_TABLESPACE=A:B
schurik
  • 7,798
  • 2
  • 23
  • 29
  • No REMAP_TABLESPACE choice.C:\Users\Administrator>imp -help – Dolphin Aug 16 '13 at 09:46
  • @Dolphin - it's a [datapump `impdp`](http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm) option; if you are on 10g+ and are allowed, switch to `expdp` and `impdp`, which makes this sort of thing easy. – Alex Poole Aug 16 '13 at 09:53
  • @AlexPoole impdp ZHPE/zhpe directory=E:\镇海石化\镇海石化2013-08-15Bak dumpfile=E:\xxxx\xxxx2013-08-15Bak\130815.dmp REMAP_TABLESPACE=ECA:ZHPE REMAP_SCHEMA=ECASYS:ZHPE logfile=ZHPE.log; UDI-00014: parameter 'directory' invalid value – Dolphin Aug 16 '13 at 11:45
  • @Dolphin - `directory` is [a database object, not a file path](http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#autoId20); you need to read up on how that works, and the `dumpfile` doesn't have a path either, just a file name. You can only `impdp` a file created by `expdp` though, it's a different format to one created by `exp`. – Alex Poole Aug 16 '13 at 11:53