Oracle 客户端 使用 expdp/impdp 示例 说明
一.客户端使用expdp/impdp说明
有关Oracle expdp/impdp 命令之前也整理了一些文档，链接如下：
exp/imp 与 expdp/impdp 对比 及使用中的一些优化事项
http://blog.csdn.net/tianlesoftware/article/details/6093973
Oracle 10g Data Pump Expdp/Impdp 详解
http://blog.csdn.net/tianlesoftware/article/details/4674224
http://blog.csdn.net/tianlesoftware/article/details/6260138
Oracle expdp/impdp 从高版本 到 低版本 示例
http://blog.csdn.net/tianlesoftware/article/details/6533421
此篇文章主要说明一个观点：可以客户端使用expdp/impdp 命令。网上的很多文章都说expdp/impdp 是服务端命令，只能在服务端使用。实际上这种说法有一定的问题，先看官网的一段文字说明：
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL801
Oracle Data Pump is made up of three distinct parts:
（1）The command-lineclients, expdp and impdp
（2）The DBMS_DATAPUMP PL/SQLpackage (also known as the Data Pump API)
（3）The DBMS_METADATA PL/SQLpackage (also known as the Metadata API)
The Data Pumpclients, expdp and impdp, invoke the Data Pump Export utilityand Data Pump Import utility, respectively.
--Data Pump 客户端（exdp/impdp）调用Data Pump Export/import工具。
The expdp and impdp clientsuse the procedures provided in the DBMS_DATAPUMP PL/SQL package toexecute export and import commands, using the parameters entered at the commandline. These parameters enable the exporting and importing of data and metadatafor a complete database or for subsets of a database.
--expdp/impdp 客户端使用DBMS_DATAPUMP 包来执行导出导入操作。
When metadata ismoved, Data Pump uses functionality provided bythe DBMS_METADATA PL/SQL package. The DBMS_METADATA packageprovides a centralized facility for the extraction, manipulation, andre-creation of dictionary metadata.
The DBMS_DATAPUMP and DBMS_METADATA PL/SQLpackages can be used independently of the Data Pump clients.
All Data Pump Export and Import processing,including the reading and writing of dump files, is done on the system (server)selected by the specified database connect string.
--所有的dump 文件都会保存在server 上指定的目录里。
This means thatfor unprivileged users, the database administrator (DBA) must create directoryobjects for the Data Pump files that are read and written on that server filesystem.
以上的文字总结如下：
DataPump 客户端命令（expdp/impdp）会调用DBMS_DATAPUMP PL/SQLpackage 和DBMS_METADATA PL/SQL包，这2个包是在server 上的，我们在客户端上执行expdp/impdp。 但是所生成的dump 文件还是存在与server 上指定的directory上。
因此，并不是网上传说的，expdp/impdp 只能在服务端使用。
二.客户端使用expdp/impdp示例
服务端版本：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 -Production
NLSRTL Version 11.2.0.3.0 - Production
在服务端创建directory：
SQL> create directory backup as'/u01/backup';
Directory created.
SQL> grant read,write on directory backupto dave;
Grant succeeded.
在客户端的tnsnames.ora里配置如下：
DG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.11)(PORT = 1521))
)
(CONNECT_DATA =
(SID = dg)
(SERVER = DEDICATED)
)
)
在安装server 版本的windows 平台执行expdp命令：
C:\Users\Administrator.DavidDai>expdp dave/dave@dg directory=backup dumpfile=dave.dmp logfile=dave.log schemas=dave
Export: Release11.2.0.1.0 - Production on Thu Dec 15 16:41:56 2011
--注意使用的是11g的expdp
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
Starting"DAVE"."SYS_EXPORT_SCHEMA_01": dave/********@dg directory=backupdumpfile=dave.dmp logfile=dave.log schemas=dave
Estimate in progress using BLOCKS method...
Processing object typeSCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object typeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object typeSCHEMA_EXPORT/TABLE/TABLE
Processing object typeSCHEMA_EXPORT/TABLE/COMMENT
Processing object typeSCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object typeSCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object typeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported"DAVE"."DAVE" 6.539 KB 31 rows
Master table"DAVE"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DAVE.SYS_EXPORT_SCHEMA_01is:
/u01/backup/dave.dmp
Job"DAVE"."SYS_EXPORT_SCHEMA_01" successfully completed at16:44:11
成功导出。我们查看一下/u01/backup 目录：
[root@DG1 u01]# cd /u01/backup/
[root@DG1 backup]# ll
total 168
-rw-r----- 1 oracle oinstall 163840 Dec 1516:44 dave.dmp
-rw-r--r-- 1 oracle oinstall 1322 Dec 15 16:44 dave.log
--清空目录，为下次expdp 准备：
[root@DG1 backup]# rm -rf *
切换到只安装了oracle 10g的客户端的机器上执行expdp：
C:\Users\Administrator>expdp dave/dave@dg directory=backup dumpfile=dave.dmp logfile=dave.log schemas=dave
Export: Release10.2.0.3.0 - Production on 星期四, 15 12月, 2011 16:56:09
--注意expdp 版本：
Copyright (c) 2003, 2005, Oracle. All rights reserved.
连接到: OracleDatabase 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Miningand Real Application Testing options
启动"DAVE"."SYS_EXPORT_SCHEMA_01": dave/********@dg directory=backup dumpfile=
dave.dmp logfile=dave.log schemas=dave
正在使用 BLOCKS 方法进行估计...
处理对象类型SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 64 KB
处理对象类型SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
处理对象类型SCHEMA_EXPORT/TABLE/TABLE
处理对象类型SCHEMA_EXPORT/TABLE/COMMENT
处理对象类型SCHEMA_EXPORT/TABLE/INDEX/INDEX
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . 导出了 "DAVE"."DAVE" 6.539 KB 31 行
已成功加载/卸载了主表"DAVE"."SYS_EXPORT_SCHEMA_01"
******************************************************************************
DAVE.SYS_EXPORT_SCHEMA_01 的转储文件集为:
/u01/backup/dave.dmp
作业"DAVE"."SYS_EXPORT_SCHEMA_01" 已于 16:57:58 成功完成
查看备份文件：
[root@DG1 backup]# ll -h
total 168K
-rw-r----- 1 oracle oinstall 160K Dec 1516:57 dave.dmp
-rw-r--r-- 1 oracle oinstall 1.2K Dec 1516:57 dave.log
至此，我们有了一个用10g的expdp 导出了11g的Oracledump文件。现在我们用11g的impdp 导入该dump：
C:\Users\Administrator.DavidDai>impdp dave/dave@dg directory=backup dumpfile=dave.dmp logfile=imp.log schemas=davetable_exists_action=replace
Import: Release 11.2.0.1.0 - Production on Thu Dec 15 17:28:43 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real ApplicationTesting options
Master table "DAVE"."SYS_IMPORT_SCHEMA_01"successfully loaded/unloaded
Starting "DAVE"."SYS_IMPORT_SCHEMA_01": dave/********@dg directory=backupdumpfile=dave.dmp logfile=imp.log schemas=dave table_exists_action=replace
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DAVE"."DAVE" 6.539 KB 31 rows
Job "DAVE"."SYS_IMPORT_SCHEMA_01" successfullycompleted at 17:29:03
导入成功。 这个说明expdp 从低到高的兼容性是没有问题，如果从高到低，在expdp时就需要执行version 参数。 这个在之前的链接有说明。
小结：
Expdp/impdp可以在客户端使用，使用时用@指定service Name 就可以了。
-------------------------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
Blog: http://blog.csdn.net/tianlesoftware
Weibo: http://weibo.com/tianlesoftware
Email: tianlesoftware@gmail.com
Skype: tianlesoftware
-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----
DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)
DBA 超级群：63306533(满); DBA4 群：83829929 DBA5群： 142216823
DBA6 群：158654907 DBA7 群：172855474
