Database link is an efficient way to copy data between two databases.It reduces time to copy small data faster. The link works using the tnsnames service with tcp/ip connection packet transfer.
Add tns entry in destination tnsnames.ora file of source database
[oracle@orcl19x ~]$ cat /apps01/product/12.1.0/dbhome_1/network/admin/tnsnames.ora|tail -9 EXDBX = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.36)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = exdbx) ) )
Check the tns connectivity using tnsping
[oracle@orcl19x ~]$ tnsping exdbx TNS Ping Utility for Linux: Version 22.214.171.124.0 - Production on 15-JUN-2021 23:10:29 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /apps01/product/12.1.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.36)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = exdbx))) OK (0 msec)
Create dblink on the destination database with the service name of source database with user credentials
SQL> create database link exdbx connect to test identified by "password" using 'exdbx'; Database link created.
Check the connection from destination to source using service created with database link
SQL> select * from dual@exdbx; D - X
Validate the count of rows to be copied from source to destination database
SQL> select count(*) from test.tabx@exdbx; COUNT(*) ---------- 1048576