ESTIMATE in Oracle Expdp

The value for parameter ESTIMATE  is either BLOCKS (default) or STATISTICS.

BLOCKS: The estimated space used is calculated by multiplying the number of database blocks used by the target objects with the appropriate block sizes. It is  the least accurate because of:

  • The table was created with a much bigger initial extent size than was needed for the actual table data
  • Many rows have been deleted from the table, or a very small percentage of each block is used.

STATISTICS: The estimated space used is calculated by using statistics for each table. If the table has been recently analyzed, the “estimate=statistics” would be the most accurate.

If a table has LOBs, ESTIMATE dump file size does NOT take LOB size into consideration.

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,

  • The table was created with a much bigger initial extent size than was needed for the actual table data.
  • 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.

Below is an example shown both in case of ESTIMATE=STATISTICS and ESTIMATE=BLOCKS. In both cases data pump export dump file is generated after estimation of dump file.

C:\>expdp system/sql123@noida directory=dpump schemas=hr logfile=hrlog11.log dumpfile=hr.dmp ESTIMATE=BLOCKS
Export: Release 11.1.0.6.0 - Production on Friday, 15 April, 2011 19:26:02
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@noida directory=dpump schemas=hr logfile=hrlog11.log ESTIMATE=BLOCKS
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "HR"."COUNTRIES" 64 KB
. estimated "HR"."DEPARTMENTS" 64 KB
. estimated "HR"."EMPLOYEES" 64 KB
. estimated "HR"."JOBS" 64 KB
. estimated "HR"."JOB_HISTORY" 64 KB
. estimated "HR"."LOCATIONS" 64 KB
. estimated "HR"."REGIONS" 64 KB
Total estimation using BLOCKS method: 448 KB
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
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "HR"."COUNTRIES" 6.375 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.015 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.984 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.484 KB 4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
D:\DPUMP\EXPDAT.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:28:16

Now we will export the schemas by using ESTIMATE=STATISTICS

C:\>expdp system/sql123@noida directory=dpump schemas=hr dumpfile=hr1.dmp logfile=hrlog111.log ESTIMATE=STATISTICS
Export: Release 11.1.0.6.0 - Production on Friday, 15 April, 2011 19:31:15
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@noida directory=dpump schemas=hr dumpfile=hr1.dmp logfile=hrlog111.log ESTIMATE=STATISTICS
Estimate in progress using STATISTICS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "HR"."EMPLOYEES" 15.91 KB
. estimated "HR"."LOCATIONS" 8.034 KB
. estimated "HR"."JOB_HISTORY" 6.861 KB
. estimated "HR"."JOBS" 6.795 KB
. estimated "HR"."DEPARTMENTS" 6.710 KB
. estimated "HR"."COUNTRIES" 6.150 KB
. estimated "HR"."REGIONS" 5.488 KB
Total estimation using STATISTICS method: 55.95 KB
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
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."JOBS" 6.984 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.015 KB 27 rows
. . exported "HR"."COUNTRIES" 6.375 KB 25 rows
. . exported "HR"."REGIONS" 5.484 KB 4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
D:\DPUMP\HR1.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:33:24

Using ESTIMATE=BLOCKS, before data pump export size is shown as 448 KB and using ESTIMATE=STATISTICS, before data pump export size is shown as 55.95 KB and my actual dump file size was 408KB which is away from estimation using ESTIMATE=BLOCKS as difference is 448-408=40 KB. In later case difference is 448-55.95=392.05KB.

Thank you for giving your valuable time to read the above information.

Source

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Tagged:

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.