Export/Import problems
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Export/Import problems

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Posts
    337

    Export/Import problems

    Hi all,

    I have done a FULL (full=y) export on a db. For the import i need to import into a schema. On the second db, i have dropped the user (cascade) and recreated the user with an empty schema. Now i generated the import with the indexfile option to generate the ddl:

    imp system file=expfull.dmp fromuser=test touser=test indexfile=index.sql statistics=none ignore=y

    In the index file i removed all the REM comments and replaced the default tablespace with the default tablespace for the user on the second db. I ran the import:

    imp system file=expfull.dmp fromuser=test touser=test statistics=none ignore=y

    I got the following errors (one of many) duringof the import:

    Column 1 3100
    Column 2 Pieter Breughelstraat 837
    Column 3 3029SK
    Column 4 Utrecht
    Column 5 Utrecht
    Column 6 NL
    IMP-00019: row rejected due to ORACLE error 2291
    IMP-00003: ORACLE error 2291 encountered
    ORA-02291: integrity constraint (NIK.LOC_C_ID_FK) violated - parent key not foun
    d

    Now the questions i have are:

    Have I done it the wrong way?
    Do contraints have to be disabled?
    Do i do this by removing the 'enable constraints' bit in the index file generated?

    Any help will be much appreciated!

    Thanks,
    Chucks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    why did you use an indexfile as opposed to just letting the import do everything you want?

    The import wouldnt enable constraints till the end

  3. #3
    Join Date
    Dec 2001
    Posts
    337
    I used the indexfile because the user i am importing too has a different tablespace. Once i edited it, i ran the script to create the empty tables et all. Is there another way of doing this?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    yes, create the user up front - set up the default tablespaces there and away you go

  5. #5
    Join Date
    Dec 2001
    Posts
    337
    well that is what i did. I dropped the user and recreated it, however the default tbs is different compared to the db from the export is coming from! Hence the need to edit the export file. Somes tables do get imported however certain rows are not inserted due to constraints!Any ideas?

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    there is no need to edit anything, the tables will go into the default tablespace of the new user

  7. #7
    Join Date
    Jul 2002
    Posts
    335
    Davey is right, but if it's that much of pain to have objects in the users default tablespace, why don't you just create new tablespaces with the same names that you had on the old database?

    I'm struggling to see the problem here...

    Bazza

  8. #8
    Join Date
    Dec 2001
    Posts
    337
    Hi guys..

    The problem i have here is that the users have different tablespaces assigned to them and has to remain this way. During the import i am getting:

    IMP-00019: row rejected due to ORACLE error 2291
    IMP-00003: ORACLE error 2291 encountered
    ORA-02291: integrity constraint (FEBSTEST.RPT_PARAMETER_MAP_REPORT_ID_FK) violat

    on sevaral of the tables. Hence i that tables rows are not imported! Any solutions guys?

    Thanks in advance

  9. #9
    Join Date
    Apr 2006
    Posts
    377
    No really...davey23uk has already provided a solution to your problem. One question though, do any of the tables you are importing have LOBS? If so, you'll need to precreate these tables.

  10. #10
    Join Date
    Dec 2001
    Posts
    337
    then why i am getting the contstraint errors?

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