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;