_____________________________________________________________________________________________________________________
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
advertisements
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
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