Oracle 18c Datapump-Estimate Parameter
Description:-
- In this article we are going to see the Oracle 18c Datapump-Estimate Parameter
ESTIMATE :
It specifies the method that Export will use to estimate how much disk space each table in the export job will consume (in bytes). The estimate is printed in the log file and displayed on the client’s standard output device. The estimate is for table row data only; it does not include metadata.
BLOCKS :
The estimate is calculated by multiplying the number of database blocks used by the target objects with the appropriate block sizes.
Note that the outcome specified by ESTIMATE=BLOCKS is far away from the size of the actual dumpfile. In fact, ESTIMATE=BLOCKS method generates more inaccurate result from dump file size when,
- a)The table was created with a much bigger initial extent size than was needed for the actual table data.
- b)Many rows have been deleted from the table, or a very small percentage of each block is used.
The outcome generated by ESTIMATE=STATISTICS is most accurate to dump file size if recently table is analyzed.
[oracle@18c empdata]$ expdp system/oracle directory=test_dir tables=hari.emp dumpfile=emp2.dmp logfile=emp2.log estimate=BLOCKS Export: Release 18.0.0.0.0 - Production on Thu Aug 23 21:09:29 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=test_dir tables=hari.emp dumpfile=emp2.dmp logfile=emp2.log estimate=BLOCKS Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . estimated "HARI"."EMP" 4.683 KB Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "HARI"."EMP" 873.1 KB 100000 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /u01/empdata/emp2.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Thu Aug 23 21:09:46 2018 elapsed 0 00:00:16
STATISTICS :
[oracle@18c empdata]$ expdp system/oracle directory=test_dir tables=hari.emp dumpfile=emp3.dmp logfile=emp3.log estimate=STATISTICS Export: Release 18.0.0.0.0 - Production on Thu Aug 23 21:10:24 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=test_dir tables=hari.emp dumpfile=emp3.dmp logfile=emp3.log estimate=STATISTICS Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . estimated "HARI"."EMP" 4.683 KB Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "HARI"."EMP" 873.1 KB 100000 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /u01/empdata/emp3.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Thu Aug 23 21:10:39 2018 elapsed 0 00:00:15
Catch Me On:- Hariprasath Rajaram
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter Group:https://www.linkedin.com/groups/10387079 Twitter: https://twitter.com/hariprasathdba
