DataPump EXPDP Estimate [ BLOCKS | STATISTICS ]Option

_____________________________________________________________________________________________________________________

This parameter is used to estimate the amount of disk space used by each table in the export. This would be an approximate value. There are two options for this parameter.

Values:- BLOCKS|STATISTICS

BLOCKS:- is the default option and by using this option the size calculates by multiplying the number of blocks used by the object.

STATISTICS:- When you this option it calculate the size by using the object statistic and as per oracle the statistics should be updated one.

NOTE:- I noticed that both options are directly or indirectly using the object statistics.

Example 1: With updated statistics. Both blocks and statistics options are showing almost same amount space for my small schema.


SQL> select last_analyzed from dba_tables where owner='TEST';

LAST_ANAL
---------
28-SEP-19
28-SEP-19
28-SEP-19
28-SEP-19
28-SEP-19
28-SEP-19
28-SEP-19

7 rows selected

expdp directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=test estimate=statistics job_name=estimate
[oracle@TESTBOX admin]$ expdp directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=test estimate=statistics job_name=estimate

Export: Release 19.0.0.0.0 - Production on Mon Sep 30 09:10:49 2019
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"."ESTIMATE":  /******** AS SYSDBA directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=test estimate=statistics job_name=estimate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "TEST"."EMPLOYEE"                           8.244 KB
.  estimated "TEST"."PROJECT_PARTICIPATION"              6.968 KB
.  estimated "TEST"."PROJECT"                            6.433 KB
.  estimated "TEST"."DEPARTMENT"                         5.886 KB
.  estimated "TEST"."SALARYGRADE"                        5.857 KB
.  estimated "TEST"."ROLE"                               5.480 KB
.  estimated "TEST"."BONUS"                              4.683 KB
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "TEST"."EMPLOYEE"                           8.734 KB      14 rows
. . exported "TEST"."PROJECT_PARTICIPATION"              7.265 KB      14 rows
. . exported "TEST"."PROJECT"                            6.609 KB       4 rows
. . exported "TEST"."DEPARTMENT"                         6.007 KB       4 rows
. . exported "TEST"."SALARYGRADE"                        6.007 KB       5 rows
. . exported "TEST"."ROLE"                               5.554 KB       3 rows
. . exported "TEST"."BONUS"                                  0 KB       0 rows
Master table "SYS"."ESTIMATE" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.ESTIMATE is:
  /u02/exp/exp.dmp
Job "SYS"."ESTIMATE" successfully completed at Mon Sep 30 09:12:19 2019 elapsed 0 00:01:24

Example 2: Without table statistics. Option used STATISTICS
Dropping the table statistics first and proceed with export. Both estimate shows the same result.

SQL> EXEC dbms_stats.delete_schema_stats('TEST');

PL/SQL procedure successfully completed.

SQL>  select last_analyzed from dba_tables where owner='TEST';

LAST_ANAL
---------








7 rows selected.

[oracle@TESTBOX admin]$ expdp directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=test estimate=statistics job_name=estimate

Export: Release 19.0.0.0.0 - Production on Mon Sep 30 09:27:13 2019
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"."ESTIMATE":  /******** AS SYSDBA directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=test estimate=statistics job_name=estimate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "TEST"."BONUS"                              4.683 KB
.  estimated "TEST"."DEPARTMENT"                         4.683 KB
.  estimated "TEST"."EMPLOYEE"                           4.683 KB
.  estimated "TEST"."PROJECT"                            4.683 KB
.  estimated "TEST"."PROJECT_PARTICIPATION"              4.683 KB
.  estimated "TEST"."ROLE"                               4.683 KB
.  estimated "TEST"."SALARYGRADE"                        4.683 KB
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "TEST"."BONUS"                                  0 KB       0 rows
. . exported "TEST"."DEPARTMENT"                         6.007 KB       4 rows
. . exported "TEST"."EMPLOYEE"                           8.734 KB      14 rows
. . exported "TEST"."PROJECT"                            6.609 KB       4 rows
. . exported "TEST"."PROJECT_PARTICIPATION"              7.265 KB      14 rows
. . exported "TEST"."ROLE"                               5.554 KB       3 rows
. . exported "TEST"."SALARYGRADE"                        6.007 KB       5 rows
Master table "SYS"."ESTIMATE" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.ESTIMATE is:
  /u02/exp/exp.dmp
Job "SYS"."ESTIMATE" successfully completed at Mon Sep 30 09:28:27 2019 elapsed 0 00:00:59

Example 3: Without table statistics. Option used BLOCKS.
So, I would recommend to update the object statistics before using this option.

[oracle@TESTBOX admin]$ expdp directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=test estimate=blocks job_name=estimate

Export: Release 19.0.0.0.0 - Production on Mon Sep 30 09:28:52 2019
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"."ESTIMATE":  /******** AS SYSDBA directory=dp_dir dumpfile=exp.dmp logfile=exp.log schemas=test estimate=blocks job_name=estimate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "TEST"."BONUS"                              4.683 KB
.  estimated "TEST"."DEPARTMENT"                         4.683 KB
.  estimated "TEST"."EMPLOYEE"                           4.683 KB
.  estimated "TEST"."PROJECT"                            4.683 KB
.  estimated "TEST"."PROJECT_PARTICIPATION"              4.683 KB
.  estimated "TEST"."ROLE"                               4.683 KB
.  estimated "TEST"."SALARYGRADE"                        4.683 KB
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "TEST"."BONUS"                                  0 KB       0 rows
. . exported "TEST"."DEPARTMENT"                         6.007 KB       4 rows
. . exported "TEST"."EMPLOYEE"                           8.734 KB      14 rows
. . exported "TEST"."PROJECT"                            6.609 KB       4 rows
. . exported "TEST"."PROJECT_PARTICIPATION"              7.265 KB      14 rows
. . exported "TEST"."ROLE"                               5.554 KB       3 rows
. . exported "TEST"."SALARYGRADE"                        6.007 KB       5 rows
Master table "SYS"."ESTIMATE" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.ESTIMATE is:
  /u02/exp/exp.dmp
Job "SYS"."ESTIMATE" successfully completed at Mon Sep 30 09:30:00 2019 elapsed 0 00:01:01

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-19 All Rights Reserved | Site Map | Contact | Disclaimer | Google