Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have two database, says A and B. They are same, expect the data records are newest or old. I want to exp from the newest one (A) and imp to the older one (B).

when I import to the old one by the following command

imp username/password file=xxx.dmp fromuser=Auser touser=Buser

this will not actually import as the B already have all the tables.

So any one know is there a command for import and replace the current DB. Thank you

By the way, I am using Oracle

share|improve this question

migrated from stackoverflow.com Sep 9 '10 at 2:04

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 2 down vote accepted

Import is intended for loading whole schemas, objects and data. Altough there is some flexibility - load individual tables, just load the data - it does not gracefully handle the existence of tables with data in the target schema. This is especially a problem if the old data in the target schema contains records which have been updated or deleted in the newer source schema.

If you are using Oracle 10g or higher the solution is to use Data Pump instead. This utility has much more powerful functionality, including the ability to replace existing tables or data in existing tables in the target schema with the data from the source schema. Find out more.

If you are using an earlier version of the database there are some workarounds. Find out more. But you may have to disable all the constraints and fix up the data in a round of post-processing.

share|improve this answer

Or, if you can afford, drop the tables in question from B and re-create them using import or impdp. We usually drop the entire schema (drop user UUUU cascade) and then make a new import, for example when we "refresh" the test/development database from production.

share|improve this answer

Just use Datapump (expdp/impdp instead of exp/imp) and specify the TABLE_EXISTS_ACTION=TRUNCATE (or REPLACE, as appropriate).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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