• Arun Kumar

Copy / refresh schema using Datapump over network (db link)

When you try to move large tables or schema between two Oracle databases, datapump export might take lot of disk space. The exported dump files may take lot of space on the disk. The best workaround is to use dblink with Oracle datapump to move data from one oracle database to another.


Note: when you use datapump with dblink, there are no dumpfiles created on disk. The data is transferred from one database to another over network.

Prepare Target Database


Even before you can jump and start copying data from one database to another, we need to prepare the target database to accept the incoming data from another database.


Create user


This step is optional, you can use sys user on target database to import schema over dblink.

SQL> create user new_scott identified by tiger;
User created.

SQL> grant connect, resource to new_scott;
Grant succeeded.

SQL> create or replace directory MY_DUMP_DIR as '/u01/dump_files';
Directory created.

SQL> grant read, write on directory MY_DUMP_DIR to new_scott;
Grant succeeded.

SQL> grant create database link to new_scott;
Grant succeeded.

Add source TNS entry


At this point, we need to make our target database able to speak to source database. Add source database TNS entry into tnsnames.ora of the target database.

devdb =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = devdb)
 )
)
 

Create DB Link


On target, we need to create a database link using the TNS entry created above.

connect new_scott/tiger
Connected.

create database link SOURCE_DB connect to scott identified by tiger  using 'devdb';
Database link created.


Import Single Schema via DB Link


Now that you have prepared the target database, its time to import source schema on target database via db link. Run below command on target database to import source schema via dblink over network.

impdp new_scott/tiger directory=MY_DUMP_DIR LOGFILE=dblink_transfer.log 
network_link=SOURCE_DB remap_schema=scott:new_scott

Where:

  • Directory = place where logfiles will be placed

  • Logfile = Name of the logfile placed in above directory

  • Network_Link = The db link to be used (created above)

  • Remap_Schema = Import source scott schema into target new_scott schema


Import Multiple Schema via DB Link


To import multiple schemas, make sure to use a DBA user (sys) to perform this action.

impdp sys directory=MY_DUMP_DIR LOGFILE=dblink_transfer.log 
network_link=SOURCE_DB schemas=IJS,scott,hr



Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Automate RMAN Backups using Shell Scripts

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback