EXPDP/IMPDP PERFORMANCE IN STANDARD EDITION

In standard edition, it is too sad that “parallel” cannot be used for speed increase.

So in this case, we may need to think in a different way to improve the performance

To reduce the IO in a single dumpfile, specify multiple dumpfiles and spread the data across multiple disks.

Also exclude STATISTICS,GRANT to save time.

Grants backup can be taken separately using a script in a spool file from sqlplus and

Statistics can be gathered using DBMS_STATS package

Specify filesize parameter to limit the size of the dumpfile to increase the dumpfiles to a particular extent but too much of dumpfiles also may reduce the performance.

dumpfile=dir1:file1.dmp,dir2:file2.dmp,dir3:file3.dmp 
logfile=exp.log 
schemas=TEST 
exclude=STATISTICS,GRANT 
filesize=100M
C:\WINDOWS\system32>expdp dumpfile=exp:exp1.dmp,exp:exp2.dmp,exp:exp3.dmp logfile=exp.log schemas=TEST exclude=STATISTICS,GRANT reuse_dumpfiles=Y filesize=100M

Export: Release 12.1.0.1.0 - Production on Tue Dec 14 17:01:11 2021

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

Username: / as sysdba

Connected to: Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA dumpfile=exp:exp1.dmp,exp:exp2.dmp,exp:exp3.dmp logfile=exp.log schemas=TEST exclude=STATISTICS,GRANT reuse_dumpfiles=Y filesize=100M
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 120 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type SCHEMA_EXPORT/TABLE/TABLE
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
. . exported "TEST"."BIG_TABLE"                          98.58 MB 1000000 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  D:\EXP1.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Dec 14 17:01:24 2021 elapsed 0 00:00:09

Leave a Reply