_____________________________________________________________________________________________________________________
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