DBLINK TO COPY TABLE OVER NETWORK

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 12.1.0.2.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