oracle数据泵导出多表,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 KB1 rows

. . exported

"PINGCHUAN"."TEST_TABLE2"4.937 KB1 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.dmp100%152KB 152.0KB/s00: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 KB1 rows

. . imported

"PINGCHUAN"."TEST_TABLE2"4.937 KB1 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;