DBLINK TO COPY TABLE OVER NETWORK

Be curious

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s