Data Pump with Network import

Datapump  is a server based bulk data movement infrastructure that supersedes the old and utilities. The old export/ import tools are still available, but do not support all and features. The new utilities are named expdp and impdp.

We need to create a directory first!

Create database directories

Execute the following commands to create a database directory. This directory must point to a valid directory on the same server as the database:

SQL>create directory dmpdir as '/opt/oracle';
Directory created.
SQL>grant read, write on directory dmpdir to scott;
Grant succeeded.

PS: Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR, that can be used:

SQL>select directory_path from dba_directories where directory_name = 'DATA_PUMP_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/app/oracle/product/10.2.0/rdbms/log/
 

Network import

With network mode imports, one doesn't need any intermediate dump files (GREAT, no more FTP'ing of dump files). Data is exported across a and imported directly into the target database.
Example:
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 dmpdir to new_scott;
Grant succeeded.
SQL> grant create database link to new_scott;
Grant succeeded.
SQL> conn new_scott/tiger
Connected.
SQL> create database link old_scott connect to scott identified by tiger using 'orcl.oracle.com';
Database link created.

impdp new_scott/tiger DIRECTORY=dmpdir NETWORK_LINK=old_scott remap_schema=scott:new_scott
All work is performed on the target system. The only reference to the source systems is via the database link.

PS:How to Create database link

遠端資料庫全域性名稱可以用以下命令查出：
SELECT * FROM GLOBAL_NAME;
修改可以用以下語句來修改引數值：
ALTER SYSTEM SET GLOBAL_NAME=TRUE/FALSE;
當資料庫引數global_name=false時，不要求資料庫連結名稱跟遠端資料庫名稱一樣。

注意：建立資料庫連結的帳號必須有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的系統許可權，用來登入到遠端資料庫的帳號必須有CREATE SESSION許可權。這兩種許可權都包含在CONNECT角色中（CREATE PUBLIC DATABASE LINK許可權在DBA中）。

如：
sql>create database link old_scott connect to scott identified by tiger
using '主機字串名';

1)dblink名(old_scott)必須與遠端資料庫的全域性資料庫名（global_name）相同；
2)使用者名稱，口令為遠端資料庫使用者名稱，口令;
3)主機字串為本機tnsnames.ora中定義的串;
4)兩個同名的資料庫間不得建立dblink;
然後，你就可以透過dblink訪問遠端資料庫了。
如：
sql>select * from 表名@old_scott;
還可以建立快照(snapshot)透過dblink實現遠端資料自動傳輸.