How to Understand and Use ESTIMATE and ESTIMATE_ONLY Parameters in DataPump

Be aware LOB segment size is not included in the estimation size of datapump.

ESTIMATE Parameter

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.

ESTIMATE_ONLY Parameter

Using estimate_only parameter will not generate the dump file other than a logfile .

The value for this parameter is either Y (yes) or N (no = default).

Y: Export estimates the space that would be consumed, but quits without actually performing the export operation.
N: Export does not only estimate, it performs an actual export of data, too.

BLOB EXCLUDED FROM ESTIMATION

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

The table segment size is 34.43GB, and the blog segment size is 38.3GB. It seems the dadapump estimation size excludes blob segment size.

SQL> select bytes/1024/1024/1024 
       from dba_segments 
       where owner='TESTUSER' 
         and segment_name='TEST' 
         and segment_type='TABLE';

BYTES/1024/1024/1024
--------------------
 34.4365234

SQL> desc TESTUSER.TEST
 Name    Null?     Type
 ------- -------  ----------
 NAME    NOT NULL VARCHAR2(30)
 CREATED NOT NULL DATE
 ......
 ......
 DOC              BLOB
 ......
 ......


SQL> select owner,table_name,column_name,segment_name 
       from dba_lobs 
      where owner='TESTUSER' and table_name='TEST';

OWNER      TABLE_NAME  COLUMN_NAME       SEGMENT_NAME 
---------- ----------  ----------------  ------------------------
TESTUSER   TEST        SESSIONITEMLONG   SYS_LOB0000062845C00010$

SQL> select owner, segment_Name,bytes/1024/1024/1024 
       from dba_segments 
      where segment_name='SYS_LOB0000062845C00010$' 
        and owner='TESTUSER';

OWNER       SEGMENT_NAME             BYTES/1024/1024/1024
----------- ------------------------ ------------------
TESTUSER    SYS_LOB0000062845C00010$  38.3


$ expdp \"/ as sysdba\" directory=DATAPUMP_DIR ESTIMATE_ONLY=y ESTIMATE=BLOCKS tables=TESTUSER.TEST;

....
....
....
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "TESTUSER"."TEST" 35.50 GB
Total estimation using BLOCKS method: 35.50 GB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:50:11

$expdp \"/ as sysdba\" directory=DATAPUMP_DIR ESTIMATE_ONLY=y ESTIMATE=STATISTICS tables=TESTUSER.TEST;
......
......
......
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "TESTUSER"."TEST" 28.96 GB
Total estimation using STATISTICS method: 28.96 GB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:51:31

From the above test, we can see LOB segment size is excluded from estimation size.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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