Copy / refresh schema using Datapump over network

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)
 )
)

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.

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

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

Was this article helpful?

Related Articles

Comments

  1. Thanks for valuable information.
    I have a doubt how long time difference between direct datapump expdp and
    DB-link .

  2. What are the limitations if we use dblink in impdp.
    Like columns having long datatype will be imported or not, etc.,

Leave a Comment