Oracle 10g Data pump and import Export performance comparison (ii) the difference between IMP and IMPDP import

Some time ago in a migration of data pump and exp, found that the difference in efficiency is quite large. Here is an example of a simple comparison.

This article compares the differences between IMP and IMPDP import.

The comparison of exp and data pump export, you can see that the data pump export speed and direct path, compared to the speed is not the essence of improvement, the following look at the data pump Import mode and imp difference.

The imported DMP file is the result of the above export, and first look at the import speed of imp:

$ sqlplus "/as SYSDBA"

Sql*plus:release 10.2.0.3.0-production on Saturday June 2 17:00:26 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connect to:

Oracle Database 10g Enterprise Edition release 10.2.0.3.0-64bit Production

With the partitioning, real application clusters, OLAP and Data Mining options

sql> drop user Zhejiang cascade;

The user has been deleted.

sql> create user Zhejiang identified by Zhejiang default tablespace Zhejiang;

User has created.

Sql> Grant Connect, resource to Zhejiang;

The authorization was successful.

Sql> Grant CREATE DATABASE link, create synonym, create view to Zhejiang;

The authorization was successful.

Sql> exit

From Oracle Database 10g Enterprise Edition release 10.2.0.3.0-64bit Production

With the partitioning, real application clusters, OLAP and Data Mining options disconnected

$ imp Zhejiang/zhejiang file=/data1/zj_regular.dmp full=y buffer=20480000 log=/data1/zj_regular.log

Import:release 10.2.0.3.0-production on Saturday June 2 17:10:34 2007

Copyright (c) 1982, +, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition release 10.2.0.3.0-64bit Production

With the partitioning, real application clusters, OLAP and Data Mining options

Export files created by export:v10.02.01 through a regular path

Import in the ZHS16GBK character set and Al16utf16 NCHAR character set has been completed

. Importing Zhejiang objects to Zhejiang

. . Importing table "A" Imported 1216 rows

.

.

.

. . Importing table ' z_invite_comm_pub ' imported 147 rows

The constraint is about to be enabled ...

The import was successfully terminated, but a warning appears.

$ ls-l/data1/zj_regular.log

-rw-r--r--1 Oracle Oinstall 38991 June 2 20:27/data1/zj_regular.log

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45550.htm

IMP import took 3 hours and 17 minutes to see the speed of IMPDP import:

$ sqlplus "/as SYSDBA"

Sql*plus:release 10.2.0.3.0-production on Saturday June 2 21:08:43 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connect to:

Oracle Database 10g Enterprise Edition release 10.2.0.3.0-64bit Production

With the partitioning, real application clusters, OLAP and Data Mining options

sql> drop user Zhejiang cascade;

The user has been deleted.

sql> create user Zhejiang identified by Zhejiang default tablespace Zhejiang;

User has created.

Sql> Grant Connect, resource to Zhejiang;

The authorization was successful.

Sql> Grant CREATE DATABASE link, create synonym, create view to Zhejiang;

The authorization was successful.

sql> Create or replace directory d_test as '/data1 ';

The directory has been created.

Sql> Grant Read, write on directory d_test to Zhejiang;

The authorization was successful.

Sql> exit

From Oracle Database 10g Enterprise Edition release 10.2.0.3.0-64bit Production

With the partitioning, real application clusters, OLAP and Data Mining options disconnected

$ IMPDP Zhejiang/zhejiang dumpfile=zj_datapump.dp directory=d_test full=y logfile=zj_datapump.log

Import:release 10.2.0.3.0-64bit Production on Sunday, 03 June, 2007 1:13:45

Copyright (c) 2003, +, Oracle. All rights reserved.