I have table X from remote Oracle DB A, and table Y in local Oracle DB B.

The data in Y is a partial copy of the data in X (X has some columns that aren't needed in Y; the columns I need in Y have the same names in both tables).

I periodically need to synchronize the data Y with what's in X (this is unidirectional - there's nothing in Y that needs to be copied back to X). Y has constraints that prevent simply dropping all the data in Y and repopulating it; I need to update what's already in 'Y', and insert any new rows from X that don't already exist in Y.

I've looked at a couple of options that won't work:

  • The copy feature in SQL Developer would copy X into a brand new table, not into Y
  • The export option would insert everything as new records, not update existing rows

What's the best way to do this?

  • 1
    How often do you need to perform the synchronization? Monthly? Weekly? Daily? Would you want it to be real-time if you could?
    – RDFozz
    Oct 27, 2017 at 20:14
  • 3
    rather than import, why not establish a db link in B pointing to Y. Then use the sql MERGE statement to pull the data from table_X@db_Y. Use the options of the MERGE to either MERGE or INSERT as needed.
    – EdStevens
    Oct 27, 2017 at 21:05
  • @RDFozz not often let's say weekly, I would like to do it automatically but for now I'm doing it manually
    – abbr
    Oct 28, 2017 at 20:54
  • @EdStevens I thought of dblink but as I'm not a dba I didn't want to risk any influence on db X (which is not mine btw and in prod), I appologize if I talked nonsense, I'm a begginner and more dev than dba.
    – abbr
    Oct 28, 2017 at 20:54
  • 1
    @abbr - a db link is noththing more than a mechanism that allows one database to act as a client process (just like sqlplus) to another database. I don't know what "influence" you think would be a "risk".
    – EdStevens
    Oct 29, 2017 at 21:46

2 Answers 2


I did it by: creating dblink from db A to DB B and using : merge into Y y using select X.columns from X@dblink x on (x.idX=y.idY) when matched then update set y.columns=x.columns --here ids are not included when not matched then insert (y.columns) values(x.columns) --ids are included x.colums and y.colums mean shared columns


Have you checked


Parameter in data pump import (impdb)

table_exists_action tips

  • While this may work, it's not clear that it will, and it's not what the OP asked for. This won't update existing rows, it will delete them.
    – RDFozz
    Oct 27, 2017 at 17:33
  • the suggested solution won't work for me because Y has constraints that prevent me from just truncating Y and inserting data from exported file from X
    – abbr
    Oct 27, 2017 at 18:41
  • This could be a great place to use a merge command.
    – Gandolf989
    Oct 27, 2017 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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