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?

Improve this question
6
  • 1
    How often do you need to perform the synchronization? Monthly? Weekly? Daily? Would you want it to be real-time if you could? Commented 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. Commented 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 Commented 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. Commented 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". Commented Oct 29, 2017 at 21:46

2 Answers 2

Reset to default
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

Improve this answer
-2

Have you checked 

table_exists_action=replace

Parameter in data pump import (impdb)

table_exists_action tips

Improve this answer
3
  • 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. Commented 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 Commented Oct 27, 2017 at 18:41
  • This could be a great place to use a merge command. Commented 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.