Data Pump 的遠端匯出資料小結

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

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

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29320885/viewspace-1130216/，如需轉載，請註明出處，否則將追究法律責任。

Data Pump 的遠端匯出資料小結

相關文章