0

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 '17 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 '17 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 '17 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 '17 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 '17 at 21:46
1

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

-2

Have you checked

table_exists_action=replace

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 '17 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 '17 at 18:41
  • This could be a great place to use a merge command. – Gandolf989 Oct 27 '17 at 19:10

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.