With help of ESTIMATE parameter of data pump export you can specify the method that Export will use to estimate how much disk space each table in the export job will consume (in bytes) before performing actual data pump export operation.
The ESTIMATE parameter can take two parameters. Either BLOCKS (default) or STATISTICS.
The meaning of these two parameter values are specified below.
BLOCKS: The estimate is calculated by multiplying the number of database blocks used by the target objects with the appropriate block sizes.
STATISTICS: The estimate is calculated using statistics for each table. So to be accurate you must analyze table recently.
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.
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.
E:\>expdp schemas=arju userid=arju/a dumpfile=arju_11_01_blocks.dmp directory=test estimate=blocks
Export: Release 10.2.0.1.0 - Production on Thursday, 07 May, 2009 11:51: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
Starting "ARJU"."SYS_EXPORT_SCHEMA_03": schemas=arju userid=arju/********
dumpfile=arju_11_01_blocks.dmp directory=test estimate=blocks
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "ARJU"."SYS_EXPORT_SCHEMA_02" 320 KB
. estimated "ARJU"."SYS_EXPORT_SCHEMA_01" 128 KB
. estimated "ARJU"."AUTHOR" 64 KB
. estimated "ARJU"."BOOKAUTHOR" 64 KB
. estimated "ARJU"."BOOKS" 64 KB
. estimated "ARJU"."BOOK_CUSTOMER" 64 KB
. estimated "ARJU"."BOOK_ORDER" 64 KB
. estimated "ARJU"."ORDERITEMS" 64 KB
. estimated "ARJU"."PROMOTION" 64 KB
. estimated "ARJU"."PUBLISHER" 64 KB
. estimated "ARJU"."T" 64 KB
Total estimation using BLOCKS method: 1024 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/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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ARJU"."SYS_EXPORT_SCHEMA_02" 214.3 KB 1125 rows
. . exported "ARJU"."SYS_EXPORT_SCHEMA_01" 31.55 KB 12 rows
. . exported "ARJU"."AUTHOR" 5.835 KB 14 rows
. . exported "ARJU"."BOOKAUTHOR" 5.609 KB 20 rows
. . exported "ARJU"."BOOKS" 7.781 KB 14 rows
. . exported "ARJU"."BOOK_CUSTOMER" 8.234 KB 21 rows
. . exported "ARJU"."BOOK_ORDER" 8.398 KB 21 rows
. . exported "ARJU"."ORDERITEMS" 6.742 KB 32 rows
. . exported "ARJU"."PROMOTION" 5.710 KB 4 rows
. . exported "ARJU"."PUBLISHER" 6.265 KB 8 rows
. . exported "ARJU"."T" 4.914 KB 1 rows
Master table "ARJU"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_SCHEMA_03 is:
E:\ORACLE\TEST\ARJU_11_01_blocks.DMP
Job "ARJU"."SYS_EXPORT_SCHEMA_03" successfully completed at 11:51:39
E:\>expdp schemas=arju userid=arju/a dumpfile=arju_11_01_statistics.dmp directory=test estimate=statistics
Export: Release 10.2.0.1.0 - Production on Thursday, 07 May, 2009 11:52: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
Starting "ARJU"."SYS_EXPORT_SCHEMA_03": schemas=arju userid=arju/********
dumpfile=arju_11_01_statistics.dmp directory=test estimate=statistics
Estimate in progress using STATISTICS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "ARJU"."SYS_EXPORT_SCHEMA_02" 187.0 KB
. estimated "ARJU"."SYS_EXPORT_SCHEMA_01" 36.67 KB
. estimated "ARJU"."BOOK_ORDER" 8.832 KB
. estimated "ARJU"."BOOK_CUSTOMER" 8.708 KB
. estimated "ARJU"."BOOKS" 8.156 KB
. estimated "ARJU"."ORDERITEMS" 6.808 KB
. estimated "ARJU"."PUBLISHER" 6.542 KB
. estimated "ARJU"."AUTHOR" 6.054 KB
. estimated "ARJU"."PROMOTION" 5.882 KB
. estimated "ARJU"."BOOKAUTHOR" 5.746 KB
. estimated "ARJU"."T" 5.061 KB
Total estimation using STATISTICS method: 285.5 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/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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ARJU"."SYS_EXPORT_SCHEMA_02" 214.3 KB 1125 rows
. . exported "ARJU"."SYS_EXPORT_SCHEMA_01" 31.55 KB 12 rows
. . exported "ARJU"."BOOK_ORDER" 8.398 KB 21 rows
. . exported "ARJU"."BOOK_CUSTOMER" 8.234 KB 21 rows
. . exported "ARJU"."BOOKS" 7.781 KB 14 rows
. . exported "ARJU"."ORDERITEMS" 6.742 KB 32 rows
. . exported "ARJU"."PUBLISHER" 6.265 KB 8 rows
. . exported "ARJU"."AUTHOR" 5.835 KB 14 rows
. . exported "ARJU"."PROMOTION" 5.710 KB 4 rows
. . exported "ARJU"."BOOKAUTHOR" 5.609 KB 20 rows
. . exported "ARJU"."T" 4.914 KB 1 rows
Master table "ARJU"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_SCHEMA_03 is:
E:\ORACLE\TEST\ARJU_11_01_STATISTICS.DMP
Job "ARJU"."SYS_EXPORT_SCHEMA_03" successfully completed at 11:52:25
Using ESTIMATE=BLOCKS, before data pump export size is shown as 1024 KB and using ESTIMATE=STATISTICS, before data pump export size is shown as 285.5 KB and my actual dump file size was 472KB which is far away from estimation using ESTIMATE=BLOCKS as difference is 1024-472=552. In later case difference is 186.5.
Note that if a table involves LOBs, the dump file size may vary as ESTIMATE does not take LOB size into consideration.
Related Documents
No comments:
Post a Comment