Estimate the dumpfile size before taking dump using data pump export

Suppose you are now in a new database. You want to do a data pump export operation in order to take a dump file. But you are not aware of the dump file size. Based on the dump file size you might choose a directory location where you will put the dump file. With expdp utility the parameter ESTIMATE_ONLY might help you in this case.

If you add ESTIMATE_ONLY=y in your data pump export operation, then Export estimates the space that would be consumed, but no dump file will be generated.

Below is an output,

$expdp arju/a@san estimate_only=y

Export: Release 10.2.0.1.0 - Production on Tuesday, 10 February, 2009 0:06:09

Copyright (c) 2003, 2005, 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 "ARJU"."SYS_EXPORT_SCHEMA_01":  arju/********@san estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "ARJU"."OBJECTS"                               13 MB
.  estimated "ARJU"."HISTORY"                               64 KB
.  estimated "ARJU"."TEST"                                  64 KB
.  estimated "ARJU"."TEST_D"                                64 KB
Total estimation using BLOCKS method: 13.18 MB
Job "ARJU"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:06:36

So it shows your dump file can be approximately around 13.18 MB. In practical it should be less then 13.18 MB.

Alternative you can query from dba_segments in order to know about the dump file size.
The dump file size for all objects of schema ARJU can be,

SQL> select sum(bytes)/1024/1024 from dba_segments where owner='ARJU';

SUM(BYTES)/1024/1024
--------------------
            13.1875

SQL> col segment_name for a30
SQL> select bytes/1024, segment_name from dba_segments where owner='ARJU';

BYTES/1024 SEGMENT_NAME
---------- ------------------------------
       64 TEST
    13312 OBJECTS
       64 HISTORY
       64 TEST_D

Alternatively, you can query group by segment_name if you want to know the size table_wise.

An important point is that the above estimation is by block method. The parameter COMPRESSION=ALL has no effect in the size of the estimation.

So, writing
$expdp arju/a@san estimate_only=y
or
$expdp arju/a@san estimate_only=y compression=ALL
will return the same size estimation.

Related Documents

Maximum Oracle Database Size.

Choose an optimal Data block size in Oracle

How to determine OS block size for windows or unix

Settting maximum Size of Backup Set or Backup Piece