This discussion is archived
8 Replies Latest reply: Jan 14, 2010 12:41 AM by Neev RSS

oracle dump file

becks Newbie
Currently Being Moderated
Hi all,

I have tried do a full export in oracle 10g2 in server A then follow by a full import do another 10g2 in server B successfully.
But when i insert a row the10g2 server A and do a full export, follow by bringing the dump file to the database in server B and do full import again using the dump file. I found out that the row inserted does not reflect in the database in server B. Is there any way i can do so that the inserted row can be reflected in serverB?

imp username/password file=dump.dmp full=y log=import.log

Thanks
cheng
  • 1. Re: oracle dump file
    726427 Newbie
    Currently Being Moderated
    Hi,

    Drop and recreate the user. Then try the import again.
  • 2. Re: oracle dump file
    Neev Explorer
    Currently Being Moderated
    Please try this
    imp username/password file=dump.dmp full=y log=import.log ignore=y

    Post output

    Thanks,
    Neerav
  • 3. Re: oracle dump file
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    You should look at your import.log file.

    If you haven't dropped the tables, the import would show failure to (re)create the tables. If there are primary key constraints, the import would show it has failed to insert duplicate rows --- rolling back the insert on the table.

    Review the options to the import command.


    Hemant K Chitale
  • 4. Re: oracle dump file
    becks Newbie
    Currently Being Moderated
    hi,

    Looking at my log file, i saw this statment tat say : following statment failed because the oblect already exists.
    By dropping the table, then try the import again it work. But i could not be keep checking which table that have new values then i drop that table. I was thinking is there other way.

    My plan is actually to have this server B have the same database as the server A. Once in a while will go to server A grab a dump file then transfer it over to the server B. Followed by a import into database in server B. Apprantly full export and import does not work.

    imp username/password file=dump.dmp log=import.log ignore=y

    Regarding adding a ignore=y, i will go try c if it work?

    thanks
  • 5. Re: oracle dump file
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    IGNORE=Y will ignore the presence of the table.
    However the result will that Oracle will attempt to APPEND rows from the export dump to the existing table. If you have Primary Keys defined, appending duplicate rows would fail. However, if you have tables where PKs are not defined, you would end up with duplicated rows.

    So you must carefully consider whether you want to append data.

    To be safe you could either
    a. TRUNCATE all the tables in the target "B" database
    OR
    b. DROP all the tables in the target "B" database (or drop the whole schema)
    before you begin the import.

    NOTE : Since you are copying specific schemas, you don't need to be using the FULL=Y option but OWNER= in the Export and, optionally, FROMUSER=, TOUSER= in the Import.

    Hemant K Chitale
    http://hemantoracledba.blogspot.com
  • 6. Re: oracle dump file
    Neev Explorer
    Currently Being Moderated
    Agree to Hemant K Chitale .
  • 7. Re: oracle dump file
    becks Newbie
    Currently Being Moderated
    thanks hemant,

    ignore=y will ignore the prensence of the table. I tried it out, apprantly it run slowly than i do the inital full import.
    Other than ignore=y, is there other way of append the data?

    Regarding this: To be safe you could either
    a. TRUNCATE all the tables in the target "B" database
    OR
    b. DROP all the tables in the target "B" database (or drop the whole schema)
    before you begin the import.

    Sorry i quite new to Oracle database.
    b.) there a command to drop the whole schema? so that i can do a full import.
    a.) Honestly, i also do not know what is truncate, maybe i can go find out from google.

    Regarding this: NOTE : Since you are copying specific schemas, you don't need to be using the FULL=Y option but OWNER= in the Export and, optionally, FROMUSER=, TOUSER= in the Import.

    So instead of of a full export/import. i should do sth like this.
    exp username/password file=dump.dmp log=export.log owner=username
    imp username/password file=dump.dmp log=import.log fromuser=username, touser=username

    eventhough the username is the same for both the database?
  • 8. Re: oracle dump file
    Neev Explorer
    Currently Being Moderated
    adding ignore=y to your script.
    Does this help you to achieve your target?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points