expdp与exp不同,远程登入用户执行的导出并非将数据导出到本地,而是导出到远程的服务器端:
[oracle@localhost ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
EXPDP_TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.37.1.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl_s)
)
)
服务端需创建导出目录
SQL> create directory expdp_dir as '/u01/expdp_dir';
Directory created.
SQL> grant read,write on directory expdp_dir to scott;
Grant succeeded.
[oracle@localhost ~]$ expdp scott/oracle@expdp_tns directory=expdp_dir dumpfile=sco
tt_test.dmp tables=emp,dept nologfile=y
Export: Release 10.2.0.1.0 - Production on Thursday, 27 March, 2014 4:37:14
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_EXPORT_TABLE_01": scott/********@expdp_tns directory=expdp_dir dumpfile=scott_test.dmp tables=emp,dept nologfile=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/expdp_dir/scott_test.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 20:37:35
此时客户端查看不到导出数据的:
[oracle@localhost1 ~]$ ls /u01/expdp_dir/
服务端查看一下,确认数据tns方式导出的仍然放在服务端directory指定的路径下:
[oracle@localhost2 ~]$ ls /u01/expdp_dir/
scott_test.dmp
expdp中的network_link参数可以远程导出数据到客户端:
客户端配置好客户端访问服务端的服务:
SQL> create public database link expdp_link connect to scott identified by oracle using 'expdp_test';
Database link created.
注:此处必须创建public的database link,官方文档中给出以下解释:
不然会报错:
ORA-39001: invalid argument value
ORA-39200: Link name "expdp_link2" is invalid.
ORA-02019: connection description for remote database not found
SQL> select tname from tab@expdp_link2;
TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
TEST
DG_TEST
[oracle@localhost ~]$ expdp scott/oracle network_link=expdp_link directory=expdp_di
r dumpfile=scott_dept.dmp nologfile=y tables=dept
Export: Release 10.2.0.1.0 - Production on Wednesday, 26 March, 2014 20:26:12
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_EXPORT_TABLE_01": scott/******** network_link=expdp_link directory=expdp_dir dumpfile=scott_dept.dmp nologfile=y tables=dept
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
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/expdp_dir/scott_dept.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 20:26:27
[oracle@localhost ~]$ ls /u01/expdp_dir/
scott_dept.dmp