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
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.
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.
Just use Datapump (expdp/impdp instead of exp/imp) and specify the TABLE_EXISTS_ACTION=TRUNCATE (or REPLACE, as appropriate).
