Data Pump Export Import Over the Network using network_link

In this article, we going to learn how to use the network_link parameter in oracle datapump export & import activity step by step.

Case:

Sometimes we need to move a table or schema from one database to another database but don't have sufficient disk space on the production server. As you know the dump file takes a lot of space on the disk.

So, the network_link parameter helps us to solve our disk space problem. Using the DB Link parameter we can perform the export and import over the network and very easily move a table or schema from an oracle database to another Oracle database very easily.

Source Side

Step 1: Create schema source side

SQL> create user ocp identified by ocp quota unlimited on users;
SQL> grant connect,resource to ocp;

Connect with OCP users and create a table and then insert some records.

SQL> conn ocp/ocp
SQL> create table t1 (id number, name varchar2(10));

SQL> insert into t1 values(10,'AARAV');
SQL> insert into t1 values(20,'SHRIPAL');
SQL> insert into t1 values(30,'SACHIN');
SQL> insert into t1 values(40,'VIKRAM');
SQL> insert into t1 values(50,'VIKASH');
SQL> commit;

Step 2: Grant export and import privileges OCP schema

Using the below query grants privileges to the user.

SQL> grant imp_full_database,exp_full_database to ocp;

Grant succeeded.

Step 3: Copy source TNS entry at target side

copy source tns entry and past it at target side in tnsnames.ora file.

My Source TNS Entry:

DIGITAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = digital)
    )
  )

Target Side

Step 1: Adding TNS entry in tnsnames.ora

After adding the tns entry, my tnsnames.ora file looks like below:

DIGITAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = digital)
    )
  )


TECH =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tech)
    )
  )

Step 2: Create a directory

Create a directory for the export backup on the target side.

SQL> create directory dump_dir as '/u01/EXPORT_BKP';

Directory created.

Step 3: Create DB Link

It's time to create a DB Link using the source service name and schema name, which you want to move in another database like OCP.

SQL> create database link digital_DB connect to ocp identified by ocp  using 'digital';

Database link created.

Step 4: Import schema using a DB link

The below command helps us to import schemas directly from the source Database. Datapump with network_link.

$ impdp directory=dump_dir logfile=ocp_imp.log network_link=digital_DB schemas=ocp

Import: Release 12.2.0.1.0 - Production on Sun Dec 26 12:54:34 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Step 5: Export schema using Datapump network_link parameter

Using the below command you can perform the export using the Database link.

$ expdp directory=dump_dir logfile=ocp_exp.log network_link=digital_DB dumpfile=ocp_exp.dmp schemas=ocp

Export: Release 12.2.0.1.0 - Production on Sun Dec 26 13:11:24 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Thanks for reading, I hope the article is helpful for you, if yes please subscribe to us on YouTube.

You Must Read:

• How to Get DB Link DDL in oracle
• Check Oracle Database Growth per Month
• How do I create a database link in Oracle?
• Global Temporary Tables
• RMAN BACKUP SCRIPT