【EXPDP】使用EXPDP备份数据时预估大小——ESTIMATE参数

登录注册

【EXPDP】使用EXPDP备份数据时预估大小——ESTIMATE参数

来源:网络
更新日期:2020-08-12

摘要：建站服务器使用EXPDP在完成数据导出时，可以使用ESTIMATE参数评估待导出数据库对象的大小，简单演示一下，供参考。1.查看有关ESTIMATE参数的

建站服务器使用EXPDP在完成数据导出时，可以使用ESTIMATE参数评估待导出数据库对象的大小，简单演示一下，供参考。

1.查看有关ESTIMATE参数的帮助信息
1）查看命令行帮助信息
ora10g@secDB /expdp$ expdp help=y
……
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
……

2）Oracle官方文档中关于ESTIMATE参数的描述
参考链接：http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#sthref105
引用内容如下：
ESTIMATE

Default: BLOCKS

Purpose

Specifies the method that Export will use to estimate how much disk space each table in the export job will consume (in bytes). The estimate is printed in the log file and displayed on the client\'s standard output device. The estimate is for table row data only; it does not include metadata.

Syntax and Description

ESTIMATE={BLOCKS | STATISTICS}

BLOCKS - The estimate is calculated by multiplying the number of database blocks used by the source objects, times the appropriate block sizes.

STATISTICS - The estimate is calculated using statistics for each table. For this method to be as accurate as possible, all tables should have been analyzed recently.

Restrictions

If the Data Pump export job involves compressed tables, the default size estimation given for the compressed table is inaccurate when ESTIMATE=BLOCKS is used. This is because the size estimate does not reflect that the data was stored in a compressed form. To get a more accurate size estimate for compressed tables, use ESTIMATE=STATISTICS.

Example

The following example shows a use of the ESTIMATE parameter in which the estimate is calculated using statistics for the employees table:

> expdp hr/hr TABLES=employees ESTIMATE=STATISTICS DIRECTORY=dpump_dir1
 DUMPFILE=estimate_stat.dmp

2.显式指定ESTIMATE参数为BLOCKS
ora10g@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec.dmp ESTIMATE=BLOCKS

Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 10 April, 2010 6:23:26

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting SEC.SYS_EXPORT_SCHEMA_01: sec/******** directory=expdp_dir dumpfile=sec.dmp ESTIMATE=BLOCKS
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated SEC.TEST_SEC                                2 MB
. estimated SEC.TEST_SEC1                               2 MB
. estimated SEC.TEST_SEC2                               2 MB
Total estimation using BLOCKS method: 6 MB
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported SEC.TEST_SEC                            1.018 MB   11716 rows
. . exported SEC.TEST_SEC1                           1.018 MB   11717 rows
. . exported SEC.TEST_SEC2                           1.018 MB   11718 rows
Master table SEC.SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/sec.dmp
Job SEC.SYS_EXPORT_SCHEMA_01 successfully completed at 06:23:37

此时预估单位是按照数据库块进行统计的，相比最终导出的大小来说不是很准确，不过有一定的参考意义。

3.指定ESTIMATE参数为STATISTICS
ora10g@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec.dmp ESTIMATE=STATISTICS

Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 10 April, 2010 6:22:02

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting SEC.SYS_EXPORT_SCHEMA_01: sec/******** directory=expdp_dir dumpfile=sec.dmp ESTIMATE=STATISTICS
Estimate in progress using STATISTICS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated SEC.TEST_SEC                            9.558 KB
. estimated SEC.TEST_SEC1                           9.558 KB
. estimated SEC.TEST_SEC2                           9.558 KB
Total estimation using STATISTICS method: 28.67 KB
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported SEC.TEST_SEC                            1.018 MB   11716 rows
. . exported SEC.TEST_SEC1                           1.018 MB   11717 rows
. . exported SEC.TEST_SEC2                           1.018 MB   11718 rows
Master table SEC.SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/sec.dmp
Job SEC.SYS_EXPORT_SCHEMA_01 successfully completed at 06:22:14

此时的统计结果比较离谱，是何原因？
真实的原因是在使用ESTIMATE=STATISTICS参数预估时，数据来源是表的分析数据，如果表未曾分析过或分析数据不准确就会导致此处的数据失真。

4.在数据库中对sec用户做一下分析，然后再完成一次数据导出
1）对sec用户进行分析
sys@ora10g> exec dbms_stats.gather_schema_stats(OWNNAME=>\'SEC\',ESTIMATE_PERCENT=>10,DEGREE=>4,cascade=>true);

PL/SQL procedure successfully completed.

2）再次导出sec用户下的数据
ora10g@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec.dmp ESTIMATE=STATISTICS

Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 10 April, 2010 6:30:20

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting SEC.SYS_EXPORT_SCHEMA_01: sec/******** directory=expdp_dir dumpfile=sec.dmp ESTIMATE=STATISTICS
Estimate in progress using STATISTICS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated SEC.TEST_SEC2                           993.6 KB
. estimated SEC.TEST_SEC1                           993.6 KB
. estimated SEC.TEST_SEC                            993.5 KB
Total estimation using STATISTICS method: 2.910 MB
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported SEC.TEST_SEC2                           1.018 MB   11718 rows
. . exported SEC.TEST_SEC1                           1.018 MB   11717 rows
. . exported SEC.TEST_SEC                            1.018 MB   11716 rows
Master table SEC.SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/sec.dmp
Job SEC.SYS_EXPORT_SCHEMA_01 successfully completed at 06:30:35

OK，此时的预估数据与最后的生成数据大小较为接近。

5.不指定ESTIMATE参数
ora10g@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec.dmp

Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 10 April, 2010 7:26:54

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting SEC.SYS_EXPORT_SCHEMA_01: sec/******** directory=expdp_dir dumpfile=sec.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported SEC.TEST_SEC                            1.018 MB   11716 rows
. . exported SEC.TEST_SEC1                           1.018 MB   11717 rows
. . exported SEC.TEST_SEC2                           1.018 MB   11718 rows
Master table SEC.SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is:
/expdp/sec.dmp
Job SEC.SYS_EXPORT_SCHEMA_01 successfully completed at 07:27:05

在不指定ESTIMATE参数时，默认会使用BLOCKS进行估算，不过，具体的数据库对象进估算被省略，仅有以下信息提示：
Total estimation using BLOCKS method: 6 MB

6.小结
虽然ESTIMATE参数提供给我们一种预估大小的功能，不过在使用EXPDP进行数据备份时，还是尽量不要使用，毕竟所有额外的操作都是有代价的。

Good luck.

secooler
10.04.10

-- The End --