比較資料泵和exp/imp對相同資料匯出/匯入的效能差異

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


[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


要建立 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

[oracle@oel-01 exp_dump]$

Export: Release 11.2.0.1.0 - Production on Mon Jul 29

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
******************************************************************************
Dump file set for BYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/exp_dump/test.dmp
Job "BYS"."SYS_EXPORT_TABLE_01" successfully completed at



########################
BYS@ bys001>truncate table test;

Table truncated.

Elapsed: 00:00:00.16



Import: Release 11.2.0.1.0 - Production on Mon Jul 29 17:21:16 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 file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing BYS's objects into BYS
. importing BYS's objects into BYS
. . importing table                         "TEST"    1280000 rows imported
Import terminated successfully without warnings.
[oracle@oel-01 ~]$ date
Mon Jul 29 17:22:55 CST 2013


BYS@ bys001>truncate table test;

Table truncated.

Elapsed: 00:00:00.12
BYS@ bys001>drop table test purge;

Table dropped.

Elapsed: 00:00:00.05

[oracle@oel-01 exp_dump]$

Import: Release 11.2.0.1.0 - Production on Mon Jul 29 17:39:08 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
Master table "BYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "BYS"."SYS_IMPORT_FULL_01":  bys/******** directory=exp_dump dumpfile=test.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "BYS"."TEST"                                111.6 MB 1280000 rows
Job "BYS"."SYS_IMPORT_FULL_01" successfully completed at 17:40:28

EXPDP/IMPDP是Oracle推薦的資料泵匯入匯出工具，用於代替傳統的EXP/IMP，只能在服務端使用，效率比EXP/IMP快幾十倍，有續傳功能和並行功能。
這個工具始於Oracle10g，從Oracle11g開始不再提供老的EXP/IMP的諮詢但工具還可以使用。
EXPDP工具的效率差不多比EXP快幾倍，IMPDP工具的效率差不多比IMP快幾十倍，這兩個工具適用於大資料匯入匯出的情境。
匯出檔案的格式更接近於資料庫本身的檔案格式，避免了資料寫入檔案時的轉換
直接路徑載入，跳過SGA記憶體區，直接載入到高水位線之後
中繼資料metadata和資料data在匯出的過程中可以重疊進行，提高匯出的效率。
在使用EXPDP/IMPDP之前需要定義一個目錄對象，告知EXPDP/IMPDP工具匯出的檔案和匯入的檔案的存放目錄