8 Replies Latest reply: Jan 14, 2010 2:41 AM by Neev RSS

    oracle dump file

    becks
      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
          Hi,

          Drop and recreate the user. Then try the import again.
          • 2. Re: oracle dump file
            Neev
            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
              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
                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
                  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
                    Agree to Hemant K Chitale .
                    • 7. Re: oracle dump file
                      becks
                      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
                        adding ignore=y to your script.
                        Does this help you to achieve your target?