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