Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2007
    Posts
    5

    Unanswered: newbie question: how to use imp

    Hi,
    warning: I am totally newbie with oracle ;-).
    I read a documentation about how to move data from db to another. But it doesn't work so far...

    I use these commands:
    Export:
    exp USERID=system@db1 file=e:\file1.dmp log=ex.log compress=no full=yes statistics=none consistent=yes
    Import:
    imp USERID=system@db1 file=e:\file1.dmp log=im.log full=yes ignore=yes feedback=50

    The result is always the same: The export file seems to be created correctly but when I try to import I get a lot of error messages like this:
    IMP-00019: Zeile zurückgewiesen aufgrund von Oracle-Fehler 1
    IMP-00003: Oracle-Fehler 1 gefunden
    ORA-00001: Verstoß gegen Eindeutigkeit, Regel (OE.PRODUCT_INFORMATION_PK)

    Translated from german:
    IMP-00019: Line rejected due to Oracle error 1
    IMP-00003: Oracle error 1 found
    ORA-00001: Offence against clarity, rule (OE.PRODUCT_INFORMATION_PK)

    First I thought it could have to do with different settings on the two db's but when I try to import a dump from the same db I get the same error messages.
    Any ideas? Thank you :-)

  2. #2
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    problem is, that your import inserts data again in same table. (table exists with data). full=y on import means overwrite system catalog. use instead this fromuser=() touser=(), when database is already created . import in conventional mode means traditional insert.
    generally import is used when table is empty - with ignore=y or when table not exits. export file you can verify in log file, last row must contain "exported without warnings"
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  3. #3
    Join Date
    Jun 2007
    Posts
    5
    Thanks for your reply . But could you please give me an example? If I have this full export file and I want to restore the db with this file - how can I do this? Can you please write an example command for me?
    Thank you

  4. #4
    Join Date
    Jun 2007
    Posts
    5
    Hi,
    is there anybody who can help me? Maybe the solution is not to use IMP/EXP? I just want to keep the data of two db's the same. When I work on db1 I want to move the full db data to db2. When I work then on db2 I want to move the data to db1. I never want to work on both db's at the same time, so I thought IMP/EXP would help me.
    Any more ideas? Thank you!

  5. #5
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    sileeem:
    sorry for delay.
    you can read next page:
    http://download-uk.oracle.com/docs/c.../ch02.htm#1656

    to my practices of import:
    - i create empty database
    - create tablespaces
    - create schemas
    - grant system privileges, quotas to users
    - using import fromuser= and touser=

    you can use full import too, but:
    - you must have same disk space like in original db. when on original db on disk E: you need 5 GB than on new db too.
    - you must recreate triggers owned by sys


    or when you migrate data into test db then you can drop tables, sequences, trigers on test db and import from source db with data.
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  6. #6
    Join Date
    Jun 2007
    Posts
    5
    Hi baloo,
    thanks for your reply. In the meantime I have thought again about your message from yesterday and have found a solution that works fine for me.
    Because all tables I need to import have all the same owner I can do the following:
    1) delete userX (cascade, so all his tables will also be deleted)
    2) create userX (with the same parameters as he was created before)
    3) import with fromuser=userX touser=userX

    It's easy... but not for newbies like me. Thanks for your help!

  7. #7
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    sileem:
    instead drop user cascade you can make select and run result of this script:
    select 'drop '||x.object_type||' '||x.object_name ||' ;' from user_objects x
    you need be logged as USER_X
    when you will import more than one schema better is use fromuser=(user1,user2... ) touser =(user1,user2... ) because will imported referential constraints too
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >select 'drop '||x.object_type||' '||x.object_name ||' ;' from user_objects x
    This will work for some/many/most cases but may need to be done multiple times when PK/FK relationships exist.
    Oracle won't let you drop the table with PK until all tables with FK have been dropped.
    It can get even messier when PK/FK exist across schemas.
    With free advice, you get what you paid for it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jun 2007
    Posts
    5
    Thank you. But I think dropping the user is the easiest in my case, cuz there is only one schema to import.
    Great forum. Let's have a beer now!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •