比较数据泵和exp/imp对相同数据导出/导入的性能差异

1.创建测试表语句：128W条数据，216M。

create table test as select * from dba_objects where rownum<10001;

begin
for i in 1 .. 7 loop
insert into test select * from test;
  commit;
end loop;
  end;

BYS@ bys001>select count(*) from test;

  COUNT(*)
----------
   1280000

Elapsed: 00:00:01.12

BYS@ bys001>select segment_name,segment_type,tablespace_name,extents,bytes/1024/1024 MB,owner from dba_segments where segment_name='TEST' and owner='BYS';
SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE_NAME    EXTENTS         MB OWNER
--------------- ------------------ --------------- ---------- ---------- ----------
TEST            TABLE              USERS                   98        216 BYS

一、导出测试：

1.使用EXP导出，用时30秒。

[oracle@oel-01 ~]$ exp bys/bys file='/home/oracle/test.dmp' tables=test rows=y

Export: Release 11.2.0.1.0 - Production on Mon Jul 29 17:01:48 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           TEST    1280000 rows exported
Export terminated successfully without warnings.
[oracle@oel-01 ~]$ date
Mon Jul 29 17:02:18 CST 2013

2.使用EXPDP导出：
要创建 exp_dump目录并在数据库中指定。
[oracle@oel-01 ~]$ mkdir exp_dump

BYS@ bys001>create directory exp_dump as '/home/oracle/exp_dump';

Directory created.

Elapsed: 00:00:01.14

导出同样的数据，EXPDP用时1分13秒。

[oracle@oel-01 exp_dump]$ expdp bys/bys directory=exp_dump file=test.dmp tables=test

Export: Release 11.2.0.1.0 - Production on Mon Jul 29 17:31:51 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=test.dmp" Location: Command Line, Replaced with: "dumpfile=test.dmp"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "BYS"."SYS_EXPORT_TABLE_01":  bys/******** directory=exp_dump dumpfile=test.dmp tables=test reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BYS"."TEST"                                111.6 MB 1280000 rows
Master table "BYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************