• 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



1,193 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis