Network Link Usage

<- DataPump

Overview

One of the most powerful features of the Data Pump is the ability to import files directly from one database to another using OracleNet. The following steps detail how to implement this.

Source Database

• On the source database create a user account to be used by a database link from the destination database if it does not already exist.
• Ensure for schema refreshes the user on the source database has the EXP_FULL_DATABASE privileged.
  Example: GRANT EXP_FULL_DATABASE TO scott;

Destination Database

1. Create a Directory Object (used for log files).
2. Create and test a local tnsnames.ora service name to the source database.
3. Create and test a database link to the source database.
4. Import data from the source database using the NETWORK_LINK parameter.

Source Database (DB2)

SQL> CREATE USER dp_user IDENTIFIED BY "password";
SQL> GRANT connect,resource,exp_full_database TO dp_user;

Destination Database (DB1)

SQL> CREATE OR REPLACE DIRECTORY dpnet AS 'C:\exports';
SQL> GRANT read,write ON DIRECTORY dpnet TO system;

OS> ping db2srv
OS> tnsping DB2 10

SQL> CREATE PUBLIC DATABASE LINK dblink_db2
     CONNECT TO dp_user IDENTIFIED BY "password"
     USING 'DB2';

SQL> SELECT * FROM v$database@dblink_db2;

OS> impdp system/password JOB_NAME=ImportSchema
                          LOGFILE=import.schema.log
                          NETWORK_LINK=dblink_db2
                          SCHEMAS=SCOTT
                          TABLE_EXISTS_ACTION=REPLACE

    Enter above impdp command on one line (or use a .par file).

<- DataPump