importing to a different tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: importing to a different tablespace

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    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.

  3. #3
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Ooops.. Mistake regretted.
    -nagarjuna

  6. #6
    Join Date
    Mar 2009
    Posts
    1
    Even after setting IGNORE=Y, I am still getting the same error

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Gupta0909 View Post
    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.

  8. #8
    Join Date
    Jan 2001
    Posts
    3,131
    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
  •  



Click Here to Expand Forum to Full Width