update table with data from other table in remote oracle db

Question

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?

• RDFozz: How often do you need to perform the synchronization? Monthly? Weekly? Daily? Would you want it to be real-time if you could?
• EdStevens: 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.
• abbr: @RDFozz not often let's say weekly, I would like to do it automatically but for now I'm doing it manually
• abbr: @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.
• EdStevens: @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".

Answer 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

Answer 2

Have you checked

table_exists_action=replace

Parameter in data pump import (impdb)

table_exists_action tips

• RDFozz: 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.
• abbr: 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
• Gandolf989: This could be a great place to use a merge command.