Estimate the space need for EXPDP backup in Oracle
For estimate the space need for EXPDP backup, Oracle provide us the parameter ESTIMATE_ONLY. It will run the EXPDP job and estimate the space before running the job manually.
ESTIMATE_ONLY=[YES | NO]
Example of estimate TABLE Size
C:\windows\system32>EXPDP ESTIMATE_ONLY=YES tables=hr.test_objects
Export: Release 18.0.0.0.0 - Production on Mon Apr 13 11:47:36 2020
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: sys@xepdb1 as sysdba
Password:
Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01": sys/********@xepdb1 AS SYSDBA ESTIMATE_ONLY=YES tables=hr.test_objects
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "HR"."TEST_OBJECTS" 1.994 GB
Total estimation using BLOCKS method: 1.994 GB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Apr 13 11:47:45 2020 elapsed 0 00:00:03
Example for estimate the SCHEMA Size
C:\windows\system32>EXPDP ESTIMATE_ONLY=YES schemas=HR
Export: Release 18.0.0.0.0 - Production on Mon Apr 13 11:45:17 2020
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: sys@xepdb1 as sysdba
Password:
Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01": sys/********@xepdb1 AS SYSDBA ESTIMATE_ONLY=YES schemas=HR
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "HR"."TEST_OBJECTS" 1.994 GB
. estimated "HR"."TEST_OBJECTS1" 173 MB
. estimated "HR"."COUNTRIES" 64 KB
. estimated "HR"."DEPARTMENTS" 64 KB
. estimated "HR"."EMPLOYEES" 64 KB
. estimated "HR"."JOBS" 64 KB
. estimated "HR"."JOB_HISTORY" 64 KB
. estimated "HR"."LOCATIONS" 64 KB
. estimated "HR"."REGIONS" 64 KB
. estimated "HR"."TEST1" 64 KB
. estimated "HR"."TEST100" 64 KB
. estimated "HR"."TEST2" 64 KB
. estimated "HR"."TESTEXP" 64 KB
Total estimation using BLOCKS method: 2.163 GB
Example of Estimate full database
EXPDP ESTIMATE_ONLY=YES fULL=y