Compare the performance differences between data pump and exp/imp for the same data export/import

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,200 9, 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


Create the exp_dump directory and specify it in the database.
[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,200 9, 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
.. 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



########################
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,200 9, 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,200 9, 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
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 is a data pump import and export tool recommended by Oracle. It is used to replace the traditional EXP/IMP and can only be used on the server, which is dozens of times faster than EXP/IMP, supports the Resume function and parallel function.
This tool starts with Oracle10g and does not provide consultation on old EXP/IMP since Oracle11g, but it can also be used.
The EXPDP tool is several times faster than EXP, And the IMPDP tool is dozens times faster than IMP. These two tools are suitable for big data import and export scenarios.
The exported file format is closer to the file format of the database itself, avoiding conversion during data writing.
Directly load the path, skip the SGA memory zone, and load it directly to the high-water line.
Metadata and data can overlap during the export process to improve the export efficiency.
Before using EXPDP/IMPDP, You need to define a directory object to inform EXPDP/IMPDP of the exported files and the directory of imported files.