Oracle 10g – How to Sync Data Between Two Databases

data synchronizationoracleoracle-10gplsql

I have 2 databases DB_LIVE and DB_NEW, both have different credentials. I have a Data Read access on DB_LIVE and I am owner of DB_NEW which is a completely blank and raw db provided to me.

I want to copy a table from DB_LIVE to DB_NEW and later keep syncing them. Another caveat is I only have PL/SQL developer and no SQL*Plus. Can someone please provide me pointers to it.

Best Answer

  • on the destination database check that the origin database is listed in the tnsnames.ora
  • Create a database link on the destination database to the origin database with a user on the origin database who has been granted select on the origin table.
  • create a materialized view on the destination database which is filled with the statement select * from origin_table@origin_database
  • schedule a refresh of the materialized view according to your needs. Note that you cannot do a fast refresh of only the changed values over a database link so this solution may not scale to millions of records
Related Question