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.

  • 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

On the machine where you installed your database you should have SQL*Plus installed.

For your question. Create a database link from DB_NEW to DB_LIVE and use your Read Data user. Now you can query the data.

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.