-
importing to a different tablespace
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.
-
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.
-
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.
-nagarjuna
-
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Ooops.. Mistake regretted.
-nagarjuna
-
Even after setting IGNORE=Y, I am still getting the same error
-
Originally Posted by Gupta0909
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
I remember when this place was cool.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|