Estimate Required Disk Space for Export Using Estimate_only

In Data Pump EXPDP command use estimate only, to check the estimate the disk space required for the export backup job without performing real export backup. Before creating the export dump it's better to check dump size using estimate_only.

For example we are going to take demo of scott user.

EXPDP Estimate only

Step 1. Check segment size using below command.

SQL> select OWNER,sum(bytes/1024/1024) "size GB" from dba_segments 
where owner='SCOTT' group by owner;

OWNER                   size GB
-------------------- ----------
SCOTT                      .375

In my case there is scott schema size is 0.375 MB.

Step 2. Using ESTIMATE_ONLY parameter.

$ expdp schemas=scott directory=DATA_PUMP_DIR logfile=scott_exp.log estimate_only=y

Export: Release 12.1.0.2.0 - Production on Tue Mar 23 15:14:47 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=scott directory=DATA_PUMP_DIR logfile=scott_exp.log estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "SCOTT"."DEPT"                                 64 KB
.  estimated "SCOTT"."DHARMA"                               64 KB
.  estimated "SCOTT"."EMP"                                  64 KB
.  estimated "SCOTT"."SALGRADE"                             64 KB
.  estimated "SCOTT"."BONUS"                                 0 KB
Total estimation using BLOCKS method: 256 KB
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Mar 23 15:14:56 2021 elapsed 0 00:00:02

As it's showing estimation 256 KB

Read: How To Export Tablespaces Using Expdp Data Pump Utility?

How to use Estimate_only parameter with PARFILE

Here is a demo, how to take EXPDP backup and use estimate only parameter using parfile.

Step 1. Create a file with .par extention

$vi export_estimate.par

#Add below lines in above file.

schemas=scott
directory=DATA_PUMP_DIR
logfile=exp_estimate.log
estimate_only=y

Save above file just press Esc and shift+:wq hit enter.

Step 2. Run expdp command using below command.

$ expdp parfile=export_estimate.par

Export: Release 12.1.0.2.0 - Production on Tue Mar 23 15:45:29 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA parfile=exp_bkp.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "SCOTT"."DEPT"                                 64 KB
.  estimated "SCOTT"."DHARMA"                               64 KB
.  estimated "SCOTT"."EMP"                                  64 KB
.  estimated "SCOTT"."SALGRADE"                             64 KB
.  estimated "SCOTT"."BONUS"                                 0 KB
Total estimation using BLOCKS method: 256 KB
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Mar 23 15:45:39 2021 elapsed 0 00:00:01

Here you can see estimate size 256 KB.

expdp compression estimate_only if you like this article please write in comment box.