ESTIMATE DUMPFILE SIZE IN DATAPUMP ORACLE

If you need datapump to estimate the table or any other object block size for calculation of capacity, you can execute the expdp estimate_only combination to not take exact export but only block estimates

[oracle@orcl ~]$ expdp \'/ as sysdba\' directory=DATA_PUMP_DIR logfile=tbesti.log estimate_only=y tables=test.t1

Export: Release 12.1.0.2.0 - Production on Mon Jul 19 17:10:28 2021

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

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_TABLE_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR logfile=tbesti.log estimate_only=y tables=test.t1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "TEST"."T1"                                    64 KB
Total estimation using BLOCKS method: 64 KB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jul 19 22:40:35 2021 elapsed 0 00:00:06

To calculate estimate block size for tablespace

[oracle@orcl ~]$ expdp \'/ as sysdba\' directory=DATA_PUMP_DIR logfile=tbesti.log estimate_only=y tablespaces=users

Export: Release 12.1.0.2.0 - Production on Mon Jul 19 17:14:10 2021

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

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_TABLESPACE_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR logfile=tbesti.log estimate_only=y tablespaces=users
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "PUJA"."T"                                      5 MB
.  estimated "SCOTT"."DEPT"                                 64 KB
.  estimated "SCOTT"."EMP"                                  64 KB
.  estimated "SCOTT"."SALGRADE"                             64 KB
.  estimated "TEST"."T1"                                    64 KB
.  estimated "HR"."CONTACTS"                                 0 KB
.  estimated "SCOTT"."BONUS"                                 0 KB
.  estimated "TEST"."T"                                      0 KB
.  estimated "TEST"."T3"                                     0 KB
Total estimation using BLOCKS method: 5.25 MB
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Mon Jul 19 22:44:14 2021 elapsed 0 00:00:03

To estimate whole database size

[oracle@orcl ~]$ expdp \'/ as sysdba\' directory=DATA_PUMP_DIR logfile=tbesti.log estimate_only=y full=y

Export: Release 12.1.0.2.0 - Production on Mon Jul 19 17:16:03 2021

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

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR logfile=tbesti.log estimate_only=y full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
.  estimated "SYS"."KU$_USER_MAPPING_VIEW"                  64 KB
.  estimated "ORDDATA"."ORDDCM_DOCS"                      1.25 MB
.  estimated "WMSYS"."WM$CONSTRAINTS_TABLE$"               320 KB
.  estimated "SYS"."AUD$"                                  192 KB

To estimate the table block size and also export the table into dumpfile

[oracle@orcl ~]$ expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=tbesti.dmp logfile=tbesti.log estimate=blocks tables=test.t1 reuse_dumpfiles=Y

Export: Release 12.1.0.2.0 - Production on Mon Jul 19 17:08:39 2021

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

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_TABLE_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=tbesti.dmp logfile=tbesti.log estimate=blocks tables=test.t1 reuse_dumpfiles=Y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "TEST"."T1"                                    64 KB
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "TEST"."T1"                                 5.523 KB       1 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /oracle/base/admin/orcl/dpdump/tbesti.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jul 19 22:39:04 2021 elapsed 0 00:00:24

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