Simon Krenger

Whenever one has to move large amounts of data from one database to another, storage space might become an issue. An option to circumvent this problem is to use a database link with Oracle Datapump to move the data from one database to another. This way, the data is exported across the network and imported directly into the target database.

In this post, I will provide an example on how to move data via a Oracle Datapump and a database link. This post is based on the excellent entry in Oracle FAQ and basically comments all the steps mentioned in the article.

Preparation

Before we can export and import via a database link, we need to prepare the target database. First of all, on the new database, create a user with which you want to import a schema from another database. This step is optional if you want to move multiple schemas or use your DBA user to perform the import.

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

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

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

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

Now, create the database link which we will be using to perform the datapump import. If you are not sure about the syntax or how to create a database link, refer to the Oracle documentation for “CREATE DATABASE LINK”.

SQL> connect new_scott/tiger
Connected.

SQL> create database link OLD_DB connect to scott identified by tiger  using 'olddb.krenger.ch';
Database link created.

Import one schema

After creating the database link, you can then start the transfer using the standard “impdp” tools:

impdp new_scott/tiger directory=MY_DMP_DIR LOGFILE=dblink_transfer.log network_link=OLD_DB remap_schema=scott:new_scott

Whereas I specified the following options:

Parameter	Description
directory	The directory, where the logfile will be saved to
logfile	Name of the logfile (e.g. “dblink_transfer.log”)
network_link	The database link to be used for the import (created above using CREATE DATABASE LINK)
remap_schema	Specifies that the schema “scott” on the old database is imported into the schema “new_scott” on the new database

Import multiple schemas

To import multiple schemas, I like to work with a user that has the DBA privilege:

impdp simondba@kdb01 directory=ADMIN_DUMP_DIR LOGFILE=dblink_transfer.log network_link=OLD_DB schemas=simon,scott,hr

Parameter	Description
directory	The directory, where the logfile will be saved to
logfile	Name of the logfile (e.g. “dblink_transfer.log”)
network_link	The database link to be used for the import (created above using CREATE DATABASE LINK)
schemas	These schemas will be imported into the new database. Seperate by comma.

For more information on the datapump import tool and its options, please refer to the Oracle documentation. When importing data via a database link, the datapump import job is started on the target system (see DBA_DATAPUMP_JOBS)

All work is performed on the target system. The only reference to the source systems is via the database link.