使用expdp在远程导出数据2012-02-15 23:59:18| 分类： ORACLE | 标签： |字号大中小 订阅 当本地也有一个数据库时，获取远程数据库的数据就又多了一种方式：利用NETWORK_LINK。 用NETWORK_LINK时也有几个限制： Restrictions When the NETWORK_LINK parameter is used in conjunction with the TABLES parameter, only whole tables can be exported (not partitions of tables). The only types of database links supported by Data Pump Export are: public, fixed-user, and connected-user. Current-user database links are not supported. 1、在客户端上面修改tnsnames.ora 增加服务端的连接字符串 例如： asdf = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.201.10.10)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = sidsid) ) ) 2.在客户端创建DATABASE LINK连接要导出数据的服务端。 SQL> create public database link db10_rc connect to username identified by password using 'connect_string'; Database link created. //username和password是server端的，并且特别注意该处的connect_string 就为tnsnames.ora中的服务名. 3、在客户端创建文件夹，并给导出的用户授权。 create or replace directory dir as 'directory'; grant read,write on directory dir to username; 这里的username是客户端的用户名，用于导出数据用的。 4、使用expdp导出远程的数据到本地。 expdp user/pass network_link=db10_rc directory=trans_dir dumpfile=test1.dmp schemas=esbdev //这里的username用创建dblink的那个用户，directory也是客户端数据库创建的。 也可以直接将远程的用户导入本地用户，跳过生成DUMPFILE这一步 SQL> $impdp system/manager network_link=db10_rc directory=trans_dir tables=lyn.test remap_schema=lyn:yy Import: Release 10.2.0.1.0 - Production on Thursday, 25 March, 2010 19:50: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 "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** network_link=db10_rc directory=trans_dir t ables=lyn.test remap_schema=lyn:yy Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . imported "YY"."TEST" 1 rows Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 19:50:37 |
|
|
