需求：

A機中的oracle有pingchuan和zte兩個用戶(schema)，這兩個schema中都有數(shù)據(jù)

。

B機剛裝完oracle數(shù)據(jù)庫并建好實例，沒有數(shù)據(jù)。

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 KB1 rows

. . exported

"PINGCHUAN"."TEST_TABLE2"4.937 KB1 rows

. . exported

"ZTE"."TEST_TABLE3"4.929 KB1 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.dmp100%180KB 180.0KB/s00: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 KB1 rows

. . imported

"PINGCHUAN"."TEST_TABLE2"4.937 KB1 rows

. . imported

"ZTE"."TEST_TABLE3"4.929 KB1 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;

總結(jié)

以上是生活随笔為你收集整理的expdp导出 schema_Oracle使用数据泵在异机之间导出导入多个 schema的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。