Adding a dblink to copy data across Oracle DBs

Say for instance we need to copy a whole table’s worth of fresh data from Oracle database (db) B to db A.

First, we need to setup the dblink so that db B is visible from db A. Therefore in db A we execute:

CREATE DATABASE LINK B_DBLINK
  CONNECT TO <my_username> IDENTIFIED BY <my_password> USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <B domain or IP>)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
       (SERVICE_NAME = <B service name>)
    )
  )'
;

A quick check in db A can verify whether the dblink is actually working without any (networking) issue:

SELECT 1 FROM DUAL@B_DBLINK
;

Finally our block to copy the data across:

  BEGIN
  INSERT INTO OUR_TABLE
  (
    SELECT CA, CB, CC, CD
    FROM OUR_OTHER_TABLE@B_DBLINK
  );
  COMMIT;
  end;