Estimate the space need for EXPDP backup in Oracle

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

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.