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. This option will not create any dumpfiles and just to estimate the object size before export
[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
There are two options with this parameter
estimate=statistics | blocks
It is recommended to use statistics method rather than blocks because statistics method uses the collected statistics to calculate the object size which is accurate
[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