DATA PUMP - Parallel Import/Export

Data pump performance can be improved by using the PARALLEL parameter. This should be used in conjunction with the "%U" wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read.

H:\>expdp hr/hr@rock directory=hrdir parallel=2 dumpfile=hr_hr_%U.dmp logfile=hr1_hr2.log

Export: Release 10.2.0.1.0 - Production on Wednesday, 03 March, 2010 12:05:47
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.

Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/********@rock directory=hrdir parallel=2 dumpfile=hr_hr_%U.dmp logfile=hr1_hr2.log

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 1.25 MB

. . exported "HR"."FUND_NAV_EMAIL" 228.4 KB 7875 rows
. . exported "HR"."TEST_HWM" 81.27 KB 2676 rows
. . exported "HR"."A" 5.710 KB 10 rows
. . exported "HR"."AB" 4.929 KB 1 rows
. . exported "HR"."B" 5.695 KB 10 rows
. . exported "HR"."COUNTRIES" 6.093 KB 25 rows
. . exported "HR"."DEPARTMENTS" 6.640 KB 27 rows
. . exported "HR"."EMPLOYEES" 15.78 KB 107 rows
. . exported "HR"."EMPLOYEES_TEST" 15.71 KB 106 rows
. . exported "HR"."JOBS" 6.617 KB 19 rows
. . exported "HR"."JOB_HISTORY" 6.593 KB 10 rows
. . exported "HR"."LOCATIONS" 7.718 KB 23 rows
. . exported "HR"."REGIONS" 5.296 KB 4 rows
. . exported "HR"."TEST2" 4.937 KB 4 rows
. . exported "HR"."TEST3" 4.914 KB 1 rows
. . exported "HR"."TEST" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************

Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
D:\HR_DIR\HR_HR_01.DMP
D:\HR_DIR\HR_HR_02.DMP
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:06:56
------------------------------------------------------------------------------------------
The two dumpfiles has been created with above export data pump command. I have deleted the HR tables, and now will import data from both dumpfiles with a single parameter, check below.

H:\>impdp hr/hr@rock directory=hrdir parallel=2 dumpfile=hr_hr_%U.dmp logfile=hr1_hr2_IMPORT.log

Import: Release 10.2.0.1.0 - Production on Wednesday, 03 March, 2010 12:19:12
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production


With the Partitioning, OLAP and Data Mining options
Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_FULL_01": hr/********@rock directory=hrdir parallel=2 dumpfile=hr_hr_%U.dmp logfile=hr1_hr2_IMPORT.log
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 "HR"."FUND_NAV_EMAIL" 228.4 KB 7875 rows
. . imported "HR"."TEST_HWM" 81.27 KB 2676 rows
. . imported "HR"."A" 5.710 KB 10 rows
. . imported "HR"."AB" 4.929 KB 1 rows
. . imported "HR"."B" 5.695 KB 10 rows
. . imported "HR"."COUNTRIES" 6.093 KB 25 rows
. . imported "HR"."DEPARTMENTS" 6.640 KB 27 rows
. . imported "HR"."EMPLOYEES" 15.78 KB 107 rows
. . imported "HR"."EMPLOYEES_TEST" 15.71 KB 106 rows
. . imported "HR"."JOBS" 6.617 KB 19 rows
. . imported "HR"."JOB_HISTORY" 6.593 KB 10 rows
. . imported "HR"."LOCATIONS" 7.718 KB 23 rows
. . imported "HR"."REGIONS" 5.296 KB 4 rows
. . imported "HR"."TEST2" 4.937 KB 4 rows
. . imported "HR"."TEST3" 4.914 KB 1 rows
. . imported "HR"."TEST" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
ORA-31684: Object type PROCEDURE:"HR"."ADD_JOB_HISTORY" already exists
ORA-31684: Object type PROCEDURE:"HR"."SECURE_DML" already exists
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-31684: Object type VIEW:"HR"."EMP_DETAILS_VIEW" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"HR"."TRG_FUND_NAV_EMAIL_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."TRG_FUND_NAV_EMAIL_HISTORY" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HR"."SYS_IMPORT_FULL_01" completed with 5 error(s) at 12:23:08
H:\>
-------------------------------------------------------------------------
Without parallel clause, all data has been imported from both dumpfiles.
-------------------------------------------------------------------------
H:\>impdp hr/hr@rock directory=hrdir dumpfile=hr_hr_%U.dmp logfile=hr1_hr2_IMPORT_2.log
Import: Release 10.2.0.1.0 - Production on Wednesday, 03 March, 2010 15:27:05
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_FULL_01": hr/********@rock directory=hrdir dumpfile=hr_hr_%U.dmp logfile=hr1_hr2_IMPORT_2.log
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 "HR"."FUND_NAV_EMAIL" 228.4 KB 7875 rows
. . imported "HR"."TEST_HWM" 81.27 KB 2676 rows
. . imported "HR"."A" 5.710 KB 10 rows
. . imported "HR"."AB" 4.929 KB 1 rows
. . imported "HR"."B" 5.695 KB 10 rows
. . imported "HR"."COUNTRIES" 6.093 KB 25 rows
. . imported "HR"."DEPARTMENTS" 6.640 KB 27 rows
. . imported "HR"."EMPLOYEES" 15.78 KB 107 rows
. . imported "HR"."EMPLOYEES_TEST" 15.71 KB 106 rows
. . imported "HR"."JOBS" 6.617 KB 19 rows
. . imported "HR"."JOB_HISTORY" 6.593 KB 10 rows
. . imported "HR"."LOCATIONS" 7.718 KB 23 rows
. . imported "HR"."REGIONS" 5.296 KB 4 rows
. . imported "HR"."TEST2" 4.937 KB 4 rows
. . imported "HR"."TEST3" 4.914 KB 1 rows
. . imported "HR"."TEST" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
ORA-31684: Object type PROCEDURE:"HR"."ADD_JOB_HISTORY" already exists
ORA-31684: Object type PROCEDURE:"HR"."SECURE_DML" already exists
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEWORA-31684: Object type VIEW:"HR"."EMP_DETAILS_VIEW" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"HR"."TRG_FUND_NAV_EMAIL_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."TRG_FUND_NAV_EMAIL_HISTORY" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HR"."SYS_IMPORT_FULL_01" completed with 5 error(s) at 15:27:22
----------------------------------------------------------------------------------------------