Oracle使用資料泵在異機之間匯出匯入多個 schema

需求：

A機中的oracle有pingchuan和zte兩個使用者(schema)，這兩個schema中都有資料 。

B機剛裝完oracle資料庫並建好例項，沒有資料。

A機的IP為192.168.1.131，B機的IP為192.168.1.137。

要求把A機中的pingchuan和zte匯入到B機中。

 

（1）A機匯出

> expdp system/oracle directory=dump_dir dumpfile=exp.dmp logfile=exp.log schemas=pingchuan,zte;

 

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

 

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 "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=dump_dir dumpfile=exp.dmp logfile=exp.log schemas=pingchuan,zte

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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

. . exported "ZTE"."TEST_TABLE3"                         4.929 KB       1 rows

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

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

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /home/oracle/dump/exp.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 08:51:17

 

（2）在A機中向B機傳輸dump檔案

scp /home/oracle/dump/exp.dmp oracle@192.168.1.137:/home/oracle/dump/

Password:

exp.dmp                                       100%  180KB 180.0KB/s   00:00

 

（3）B機匯入

> impdp system/oracle directory=dump_dir dumpfile=exp.dmp logfile=exp.log schemas=pingchuan,zte;

 

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

 

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 "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=dump_dir dumpfile=exp.dmp logfile=exp.log schemas=pingchuan,zte

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

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 "PINGCHUAN"."TEST_TABLE"                    4.937 KB       1 rows

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

. . imported "ZTE"."TEST_TABLE3"                         4.929 KB       1 rows

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 08:54:53

 

（4）B機中驗證

> sqlplus / as sysdba;

SQL> select * from pingchuan.test_table;

SQL> select * from pingchuan.test_table2;

SQL> select * from zte.test_table3;