1

I've done following comands:

expdp system/password@localdb ^
schemas=SCH directory=dumps_dir dumpfile=SCH_%date%.dmp logfile=expdpSCH_%date%.log

It runs great, without errors. But when I use command:

impdp system/password@localdb ^
schemas=SCH directory=dumps_dir dumpfile=SCH_%date%.dmp logfile=impdpSCH_%date%.log

It gave me bunch of errors that tables, sequences and other stuff already exist. So I have a question, could I use such an approach or it's wrong and I should use something else?

|improve this question
  • what you are getting error? Can you share for better understanding. In which oracle version you are using in your environment. – Md Haidar Ali Khan Sep 15 '15 at 13:31
  • @MdHaidarAliKhan I just want to know is it right way to sync with this commands. I have two identical databases and I want to sync them. Errors - is common errors when you try to think schema that had been created. But is this a tool I should use? – Dracontis Sep 15 '15 at 13:59
  • I do not see why you cannot use it. You have to take the inconveniences. You can also create a script to generate a script to disable all these constraints and on to enable them again after the import. Constraints are often a problem :-) – Marco Sep 15 '15 at 14:09
0

Check the documentation for more information or type impdp help=y. There is a parameter (TABLE_EXISTS_ACTION) that defines if you want to overwrite existing objects or if you want to append.

TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

|improve this answer
  • Just be aware that the REPLACE and TRUNCATE options may fail if there are any referential constraints on the table. – andba77 Sep 15 '15 at 13:46
  • I have some sequenced. How to deal with it? I think I could remove schema and reimport it, but I feel like using wrong tools. – Dracontis Sep 15 '15 at 14:00
  • Removing the schema certainly works and impdp should create the schema before it does the import of the data. I never used the impdp to synchronise. You can also drop the sequences but first try to see if it the parameter solves the problem with the sequences too. Also instead of importing from a file you can also import through a database link in the destination database towards the source. Just use the NETWORK_LINK parameter to define this link. – Marco Sep 15 '15 at 14:06
  • I can't use network link, because of long data. So if it is ok to use impdp for such synchronization, then I'll use it. – Dracontis Sep 15 '15 at 14:22
  • Other way is transportable tablespace if the whole schema is in a separate tablespace but I cannot help/advise you on that. – Marco Sep 15 '15 at 14:27
3

No, that is not a possible way:

  • Most of the non-table objects will already exist in your schema. You already get such error messages. The import will not overwrite it.
  • You can configure how export should work if a table already exists but if there is a table that s referenced by another table then TRUNCATE and REPLACE will not work.
  • Even if there is no foreign key reference to a table indexes will slow down the import dramatically
  • A lot of other problems may occur (concerning triggers, partitions, references to other tables and maybe more)

So the only reasonable way to synchronize the schema using expdp/impdp is to drop the schema before you import.

If you don't want to drop the schema for some reason but replace the data of the tables a lot of additional operations are necessary.

|improve this answer

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.