importing to a different tablespace

[Horace]

Hi guys,

I specifically exported a large partitioned table. The tablespace it was in is TABSPACE1, which also contains many more large tables

When I import into another instance, which does not have the tablespace TABSPACE1, I get the error message

IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TABSPACE1' does not exist
Import terminated successfully with warnings.

This is fair enough, but is there a way of forcing the import into another tablespace ??

I don't particularly want to create a new tablespace, and I don't want to transport the whole TABSPACE1 tablespace.

Your thoughts would be appreciated.

[gandolf989]

If you specify ignore=y in your import the table will get created in the schema's default tablespace. Just make sure that the default tablespace is the one that you want to use. Otherwise create the table the way you want in the proper tablespace then import with ignore=y.

[nagarjuna]

It's a problem with quota. Just check you have quota on your default tablespace.

gandolf : No need for ignore=y when importing to default default tablespace. Oracle automatically imports to default tablespace if the tablespace defined with export dumpfile is not present in targer database.

[julian]

Originally posted by nagarjuna
It's a problem with quota. Just check you have quota on your default tablespace.

gandolf : No need for ignore=y when importing to default default tablespace. Oracle automatically imports to default tablespace if the tablespace defined with export dumpfile is not present in targer database.

You are wrong and GANDOLF is right.

For a single tablespace clause (say a non-partitioned table create), IMP will redirect object creation to the default tablespace for the user if the specified tablespace does not exist.

For DDL with potentially multiple tablespace clauses (say, a partitioned table create as we have in our case) Oracle will give an ORA-00959. When there are multiple tablespace clauses, IMP cannot determine which tablespace is missing and changing all tablespace clauses to the default is not a plausible solution.

[nagarjuna]

Ooops.. Mistake regretted.

[Gupta0909]

Even after setting IGNORE=Y, I am still getting the same error

[PAVB]

Even after setting IGNORE=Y, I am still getting the same error

Just do what Gandolf suggested, pre-create your table then import with ignore=y option.

[Mr.Hanky]

Or use datapump and use the REMAP_TABLESPACE parameter, I have had success with that one.
Assuming you are on 10g.

http://www.softics.ru/docs/oracle10r.../dp_import.htm