Oracle使用資料泵在異機之間匯出匯入表

Oracle使用資料泵在異機之間匯出匯入表

需求：兩臺機器上的oracle都有一個schema叫pingchuan。A機的pingchuan中有資料，B機在pingchuan中沒有資料。A機的IP為192.168.1.131，B機的IP為192.168.1.137。

要求把A機pingchuan中的所有表匯入到B機pingchuan中。

 

（1）匯出步驟：

在A機和B機中都建立dmp檔案存放路徑
> mkdir /home/oracle/dump

進sqlplus環境
> sqlplus /as sysdba
建立路徑並授權
SQL> create directory dump_dir as '/home/oracle/dump';
Directory created.
SQL> grant read, write on directory dump_dir to pingchuan;
Grant succeeded.

SQL> exit
這一步是退出sqlplus環境回到終端，在終端中執行
> expdp pingchuan/pingchuan directory=dump_dir dumpfile=exp_ping2.dmp nologfile=y include=table;

這裡directory指定了匯出路徑，dumpfile指定了匯出名稱，nologfile指定了不用儲存匯出日誌，include指定了只匯出表物件。

很快就可以看到匯出成功完成的提示：

Export: Release 10.2.0.1.0 - 64bit Production on 星期二, 23 6月, 2015 8:19:44

 

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

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "PINGCHUAN"."SYS_EXPORT_SCHEMA_01":  pingchuan/******** directory=dump_dir dumpfile=exp_ping2.dmp nologfile=y include=table

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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/STATISTICS/TABLE_STATISTICS

. . exported "PINGCHUAN"."TEST_TABLE"                    4.937 KB       1 rows

. . exported "PINGCHUAN"."TEST_TABLE2"                   4.937 KB       1 rows

Master table "PINGCHUAN"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for PINGCHUAN.SYS_EXPORT_SCHEMA_01 is:

  /home/oracle/dump/exp_ping2.dmp

Job "PINGCHUAN"."SYS_EXPORT_SCHEMA_01" successfully completed at 08:19:51

 

導完之後把exp_ping2.dmp檔案傳輸到B機中：

> scp exp_ping2.dmp oracle@192.168.1.137:/home/oracle/dump/

Password:

exp_ping2.dmp                                 100%  152KB 152.0KB/s   00:00

 

（2）匯入步驟：
進sqlplus環境
> sqlplus / as sysdba

建立路徑並授權
SQL> create directory dump_dir as '/home/oracle/dump';
Directory created.
SQL> grant read, write on directory dump_dir to pingchuan;
Grant succeeded.

SQL> exit

> impdp pingchuan/pingchuan directory=dump_dir dumpfile=exp_ping2.dmp nologfile=y include=table;

 

很快就可以看到匯入成功完成的提示:

Import: Release 10.2.0.1.0 - 64bit Production on 星期二, 23 6月, 2015 8:28:27

 

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

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Master table "PINGCHUAN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "PINGCHUAN"."SYS_IMPORT_FULL_01":  pingchuan/******** directory=dump_dir dumpfile=exp_ping2.dmp nologfile=y include=table

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "PINGCHUAN"."TEST_TABLE"                    4.937 KB       1 rows

. . imported "PINGCHUAN"."TEST_TABLE2"                   4.937 KB       1 rows

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "PINGCHUAN"."SYS_IMPORT_FULL_01" successfully completed at 08:28:28

驗證：
> sqlplus pingchuan/pingchuan;

SQL> select * from test_table;

SQL> select * from test_table2;