0

I have two oracle databases (production and lab). When I created the lab database I imported a .dmp file from production to copy the data. Now that the database has been running for a while the data is out of sync and I'd like to import again. What's the easiest way to do this?

I tried using imp again but it shows errors now since the tables are already there. I could delete the data files and re import I suppose.

Edit: This is what I've tried:

$ imp user/password file=MyExportFile.dmp

following statement failed because the object already exists:
CREATE TABLE ...
...
Import terminated successfully with warnings.
1

If you want to replace everything then the safest method is to drop all schemas and import it again using imp

drop user schema_name cascade

You can also try ignore=y parameter in conventional import utility which will ignore the error when the object with the same name exists, which is called TABLE_EXISTS_ACTION in data pump.

imp user/password@servicename file=abc.dmp IGNORE=y

In the case of data pump method, use table_exists_action parameter to skip the existing object as below:

impdp sys/password@servicename dumpfile=abc.dmp logfile=imp_skip.log tables=abc table_exists_action=truncate
  • This appears to be the answer I'm looking for. I used the imp method. Deleting the user with cascade option wiped out all the old data, ensuring that nothing would be left over that I didn't want. Then re-adding the user is very easy through the Enterprise Manager. Much easier than deleting all the datafiles and re-creating tablespaces. – Brent Sandstrom Jun 18 '18 at 20:15
1

Next time, instead of "deleting everything", you can tell imp to ignore table creation error and still load the data by specifying the IGNORE=y option:

imp user/password file=MyExportFile.dmp IGNORE=y

There are few other considerations that you should make when importing into an existing schema, as outlined in the manual. Specifically, you may need to disable referential integrity constraints if they exists, otherwise some data may be rejected.

  • I tried this method today and it didn't work the way I wanted. Data won't be deleted from the new system if it has been deleted on the source since the last import. I also noticed that if your tables don't have a PK they will have a full duplicate copy of data added to them. – Brent Sandstrom Jun 18 '18 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.