oracle10g开始，增加了impdp、expdp功能。这几天用dbblink做了一下异机上的数据导入，
这样就节省了数据导出（expdp），然后再导入的过程。

实验环境：
OS: rhel4as4
DB：oracle10gr2

源库：192.168.1.200 devdb 在源库（192.168.1.200）上创建了tt表
目的库：192.168.1.10 proddb


实验目的：

在目的库（10）上建立到源库（200）的dblink
在目的库（200）上执行impdp操作，将源库（10）中scott用户下的所有对象都导入到目的库（200）上


两个库的tnsname.ora 及listen.ora如下：

源库：192.168.1.200 devdb 上：

[oracle@sphost admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10)
)
)

ORA10HHA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10hha)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

[oracle@sphost admin]$
[oracle@sphost admin]$
[oracle@sphost admin]$ more listener.ora
# listener.ora Network Configuration File: /home/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/10.2.0)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sphost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

[oracle@sphost admin]$



目的库192.168.1.10 proddb上
[oracle@catalog admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA10HHA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10hha)
)
)

ORA10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

[oracle@catalog admin]$
[oracle@catalog admin]$
[oracle@catalog admin]$ more listener.ora
# listener.ora Network Configuration File: /home/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/10.2.0)
(PROGRAM = extproc)
)
)

ora10hha =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

[oracle@catalog admin]$



实验步骤：


在目的库192.168.1.10 proddb上
以sysdba身份登录数据库，给scott用户授予创建dblink的权限

[oracle@catalog ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 15:49:03 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> grant create database link to scott;

Grant succeeded.


在目的库192.168.1.10 proddb上创建一个到源库的dblink

SQL> create database link todevdb200 connect to scott identified by tiger using 'ORA10';

Database link created.

SQL> select count(*) from tt@todevdb200;

COUNT(*)
----------
199272
SQL>

能够查到次信息，表示dblink创建完毕


源库：192.168.1.200 devdb 有一张表tt

现在想把源库200中scott用户下的数据库导入到目的库10上

于是在目的库10上执行如下操作：


首先查看目标库(10)中的目录：

[oracle@catalog ~]$ sqlplus system/test as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 16:06:40 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> column owner format a10;
SQL> column db_link format a15;
SQL> column host format a10;
SQL> column username format a8;
SQL> select * from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- -------- ---------- ------------
SCOTT TODEV SCOTT ORA10 16-MAY-11


oracle@catalog ~]$ impdp scott/tiger schemas=SCOTT network_link=TODEV;


日志如下：

[oracle@catalog ~]$ impdp scott/tiger schemas=SCOTT network_link=TODEV;

Import: Release 10.2.0.1.0 - Production on Tuesday, 17 May, 2011 0:13:25

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01": scott/******** schemas=SCOTT network_link=TODEV
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 22.43 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "SCOTT"."DEPT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."BONUS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."SALGRADE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
. . imported "SCOTT"."TT" 199272 rows
. . imported "SCOTT"."SYS_EXPORT_SCHEMA_01" 1057 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_SCHEMA_01" completed with 5 error(s) at 00:15:04

可以看出，已经导入了tt表，为了验证
目的库192.168.1.10 proddb上执行


SQL> select count(*) from tt@todev;

COUNT(*)
----------
199272

SQL> select count(*) from tt;

COUNT(*)
----------
199272

SQL>
可以看出，目的库中已经成功导入了tt表。


注：如果要删除dblink，执行如下命令：

SQL> drop database link todev;
Database link dropped.