DBLINK TO COPY TABLE OVER NETWORK
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 188.8.131.52.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