ESTIMATE required diskspace for EXPORT backup

ESTIMATE required diskspace for EXPORT backup: During this article we are going to discuss about ESTIMATE required diskspace for EXPORT backup using ESTIMATE_ONLY parameter in Oracle Database. Here we’ll do that activity on Oracle 19c Database.

ESTIMATE_ONLY parameter in Oracle Database:

ESTIMATE_ONLY parameter is utilized in EXPDP command, to estimate the disc space required for the export job, without doing the particular export. Sometimes, we aren’t sure what is going to be the export dump size, then better to use this selection first, before taking the export backup.

Syntax and Description:

ESTIMATE_ONLY=[YES | NO]

Noted: By default ESTIMATE_ONLY parameter is ESTIMATE_ONLY=NO.

ESTIMATE required disk space for Full EXPORT Database backup :

The subsequent shows an example of using the ESTIMATE_ONLY parameter to see what quantity space an export of the complete Database will take.

Syntax and Description:

expdp ESTIMATE_ONLY=YES FULL=Y
[oracle@node-1 ~]$ expdp ESTIMATE_ONLY=YES full=y

Export: Release 19.0.0.0.0 - Production on Thu Mar 25 20:32:19 2021
Version 19.3.0.0.0

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

Username: / as sysdba
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA ESTIMATE_ONLY=YES 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 "AUDSYS"."AUD$UNIFIED":"SYS_P181"             512 KB
.  estimated "WMSYS"."WM$CONSTRAINTS_TABLE$"               320 KB
.  estimated "SYS"."AUD$"                                  256 KB
.  estimated "WMSYS"."WM$LOCKROWS_INFO$"                   192 KB
.  estimated "WMSYS"."WM$UDTRIG_INFO$"                     192 KB
.  estimated "LBACSYS"."OLS$AUDIT_ACTIONS"                  64 KB
.  estimated "LBACSYS"."OLS$DIP_EVENTS"                     64 KB
.  estimated "LBACSYS"."OLS$INSTALLATIONS"                  64 KB
.  estimated "LBACSYS"."OLS$PROPS"                          64 KB
.  estimated "SYS"."DAM_CLEANUP_EVENTS$"                    64 KB
.  estimated "SYS"."DAM_CLEANUP_JOBS$"                      64 KB
.  estimated "SYS"."DAM_CONFIG_PARAM$"                      64 KB
.  estimated "SYS"."TSDP_ASSOCIATION$"                      64 KB
.  estimated "SYS"."TSDP_CONDITION$"                        64 KB
.  estimated "SYS"."TSDP_FEATURE_POLICY$"                   64 KB
.  estimated "SYS"."TSDP_PARAMETER$"                        64 KB
.  estimated "SYS"."TSDP_POLICY$"                           64 KB
.  estimated "SYS"."TSDP_PROTECTION$"                       64 KB
.  estimated "SYS"."TSDP_SENSITIVE_DATA$"                   64 KB
.  estimated "SYS"."TSDP_SENSITIVE_TYPE$"                   64 KB
.  estimated "SYS"."TSDP_SOURCE$"                           64 KB
.  estimated "SYS"."TSDP_SUBPOL$"                           64 KB
.  estimated "SYSTEM"."REDO_DB"                             64 KB
.  estimated "SYSTEM"."REDO_LOG"                            64 KB
.  estimated "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$"        64 KB
.  estimated "WMSYS"."WM$CONS_COLUMNS$"                     64 KB
.  estimated "WMSYS"."WM$ENV_VARS$"                         64 KB
.  estimated "WMSYS"."WM$EVENTS_INFO$"                      64 KB
.  estimated "WMSYS"."WM$HINT_TABLE$"                       64 KB
.  estimated "WMSYS"."WM$MODIFIED_TABLES$"                  64 KB
.  estimated "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$"        64 KB
.  estimated "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$"       64 KB
.  estimated "WMSYS"."WM$NESTED_COLUMNS_TABLE$"             64 KB
.  estimated "WMSYS"."WM$NEXTVER_TABLE$"                    64 KB
.  estimated "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$"         64 KB
.  estimated "WMSYS"."WM$RIC_LOCKING_TABLE$"                64 KB
.  estimated "WMSYS"."WM$RIC_TABLE$"                        64 KB
.  estimated "WMSYS"."WM$RIC_TRIGGERS_TABLE$"               64 KB
.  estimated "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$"            64 KB
.  estimated "WMSYS"."WM$VERSION_HIERARCHY_TABLE$"          64 KB
.  estimated "WMSYS"."WM$VERSION_TABLE$"                    64 KB
.  estimated "WMSYS"."WM$VT_ERRORS_TABLE$"                  64 KB
.  estimated "WMSYS"."WM$WORKSPACES_TABLE$"                 64 KB
.  estimated "WMSYS"."WM$WORKSPACE_PRIV_TABLE$"             64 KB
.  estimated "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$"       64 KB
.  estimated "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0"         0 KB
.  estimated "LBACSYS"."OLS$AUDIT"                           0 KB
.  estimated "LBACSYS"."OLS$COMPARTMENTS"                    0 KB
.  estimated "LBACSYS"."OLS$DIP_DEBUG"                       0 KB
.  estimated "LBACSYS"."OLS$GROUPS"                          0 KB
.  estimated "LBACSYS"."OLS$LAB"                             0 KB
.  estimated "LBACSYS"."OLS$LEVELS"                          0 KB
.  estimated "LBACSYS"."OLS$POL"                             0 KB
.  estimated "LBACSYS"."OLS$POLICY_ADMIN"                    0 KB
.  estimated "LBACSYS"."OLS$POLS"                            0 KB
.  estimated "LBACSYS"."OLS$POLT"                            0 KB
.  estimated "LBACSYS"."OLS$PROFILE"                         0 KB
.  estimated "LBACSYS"."OLS$PROFILES"                        0 KB
.  estimated "LBACSYS"."OLS$PROG"                            0 KB
.  estimated "LBACSYS"."OLS$SESSINFO"                        0 KB
.  estimated "LBACSYS"."OLS$USER"                            0 KB
.  estimated "LBACSYS"."OLS$USER_COMPARTMENTS"               0 KB
.  estimated "LBACSYS"."OLS$USER_GROUPS"                     0 KB
.  estimated "LBACSYS"."OLS$USER_LEVELS"                     0 KB
.  estimated "SYS"."FGA_LOG$FOR_EXPORT"                    256 KB
.  estimated "SYS"."SQL$TEXT_DATAPUMP"                     192 KB
.  estimated "SYS"."SQLOBJ$DATA_DATAPUMP"                  192 KB
.  estimated "SYS"."SQL$_DATAPUMP"                         128 KB
.  estimated "SYS"."SQLOBJ$AUXDATA_DATAPUMP"               128 KB
.  estimated "SYS"."SQLOBJ$PLAN_DATAPUMP"                  128 KB
.  estimated "SYS"."SQLOBJ$_DATAPUMP"                      128 KB
.  estimated "SYSTEM"."SCHEDULER_JOB_ARGS"                 128 KB
.  estimated "SYSTEM"."SCHEDULER_PROGRAM_ARGS"             128 KB
.  estimated "SYS"."AUDTAB$TBS$FOR_EXPORT"                  64 KB
.  estimated "SYS"."DBA_SENSITIVE_DATA"                     64 KB
.  estimated "SYS"."DBA_TSDP_POLICY_PROTECTION"             64 KB
.  estimated "SYS"."NACL$_ACE_EXP"                          64 KB
.  estimated "SYS"."NACL$_HOST_EXP"                         64 KB
.  estimated "SYS"."NACL$_WALLET_EXP"                       64 KB
.  estimated "MDSYS"."RDF_PARAM$"                           16 KB
.  estimated "WMSYS"."WM$EXP_MAP"                           16 KB
.  estimated "WMSYS"."WM$METADATA_MAP"                      16 KB
.  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 "SCOTT"."DEPT"                                 64 KB
.  estimated "SCOTT"."EMP"                                  64 KB
.  estimated "SCOTT"."SALGRADE"                             64 KB
.  estimated "SCOTT"."BONUS"                                 0 KB
Total estimation using BLOCKS method: 6.421 MB
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Thu Mar 25 20:36:54 2021 elapsed 0 00:04:19

ESTIMATE required disk space for a Schema/User EXPORT backup :

The subsequent shows an example of using the ESTIMATE_ONLY parameter to see what quantity space an export of a Schema will take.

Syntax and Description:

expdp ESTIMATE_ONLY=YES SCHEMAS=SCHEMA_NAME
[oracle@node-1 ~]$ expdp ESTIMATE_ONLY=YES SCHEMAS=SCOTT

Export: Release 19.0.0.0.0 - Production on Thu Mar 25 20:45:40 2021
Version 19.3.0.0.0

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

Username: / as sysdba
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA ESTIMATE_ONLY=YES SCHEMAS=SCOTT
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "SCOTT"."DEPT"                                 64 KB
.  estimated "SCOTT"."EMP"                                  64 KB
.  estimated "SCOTT"."SALGRADE"                             64 KB
.  estimated "SCOTT"."BONUS"                                 0 KB
Total estimation using BLOCKS method: 192 KB
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Mar 25 20:46:24 2021 elapsed 0 00:00:27

To ESTIMATE required disk space for a Table EXPORT backup :

Syntax and Description:

expdp ESTIMATE_ONLY=YES SCHEMAS=SCHEMA_NAME.TABLE_NAME
[oracle@node-1 ~]$ expdp ESTIMATE_ONLY=YES TABLES=SCOTT.EMP

Export: Release 19.0.0.0.0 - Production on Thu Mar 25 20:48:14 2021
Version 19.3.0.0.0

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

Username: / as sysdba
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA ESTIMATE_ONLY=YES TABLES=SCOTT.EMP
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "SCOTT"."EMP"                                  64 KB
Total estimation using BLOCKS method: 64 KB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Thu Mar 25 20:48:52 2021 elapsed 0 00:00:19

I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.

Leave a Comment