Sync an existing database using a .dmp file

oracleoracle-10g-r2

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.

Best Answer

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
Related Question