Data Pump with Network import

Datapump  is a server based bulk data movement infrastructure that supersedes the old import and export utilities. The old export/ import tools are still available, but do not support all Oracle 10g and 11g 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 database link 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实现远程数据自动传输.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/628922/viewspace-714771/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/628922/viewspace-714771/