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;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29485627/viewspace-1708607/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29485627/viewspace-1708607/