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
    (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
      (SERVICE_NAME = exdbx)

Check the tns connectivity using tnsping

[oracle@orcl19x ~]$ tnsping exdbx

TNS Ping Utility for Linux: Version - Production on 15-JUN-2021 23:10:29

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = = 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;


Validate the count of rows to be copied from source to destination database

SQL> select count(*) from test.tabx@exdbx;


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