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;

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

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