建站服務(wù)器 使用EXPDP在完成數(shù)據(jù)導(dǎo)出時，可以使用ESTIMATE參數(shù)評估待導(dǎo)出數(shù)據(jù)庫對象的大小，簡單演示一下，供參考。

1.查看有關(guān)ESTIMATE參數(shù)的幫助信息
1）查看命令行幫助信息
ora10g@secDB /expdp$ expdp help=y
……
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
……

2）Oracle官方文檔中關(guān)于ESTIMATE參數(shù)的描述
參考鏈接：http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#sthref105
引用內(nèi)容如下：
ESTIMATE

Default: BLOCKS

邳州網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)建站！從網(wǎng)頁設(shè)計、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、成都響應(yīng)式網(wǎng)站建設(shè)公司等網(wǎng)站項目制作，到程序開發(fā)，運營維護。創(chuàng)新互聯(lián)建站成立與2013年到現(xiàn)在10年的時間，我們擁有了豐富的建站經(jīng)驗和運維經(jīng)驗，來保證我們的工作的順利進行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)建站。

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參數(shù)為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

此時預(yù)估單位是按照數(shù)據(jù)庫塊進行統(tǒng)計的，相比最終導(dǎo)出的大小來說不是很準確，不過有一定的參考意義。

3.指定ESTIMATE參數(shù)為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

此時的統(tǒng)計結(jié)果比較離譜，是何原因？
真實的原因是在使用ESTIMATE=STATISTICS參數(shù)預(yù)估時，數(shù)據(jù)來源是表的分析數(shù)據(jù)，如果表未曾分析過或分析數(shù)據(jù)不準確就會導(dǎo)致此處的數(shù)據(jù)失真。

4.在數(shù)據(jù)庫中對sec用戶做一下分析，然后再完成一次數(shù)據(jù)導(dǎo)出
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）再次導(dǎo)出sec用戶下的數(shù)據(jù)
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，此時的預(yù)估數(shù)據(jù)與最后的生成數(shù)據(jù)大小較為接近。

5.不指定ESTIMATE參數(shù)
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參數(shù)時，默認會使用BLOCKS進行估算，不過，具體的數(shù)據(jù)庫對象進估算被省略，僅有以下信息提示：
Total estimation using BLOCKS method: 6 MB

6.小結(jié)
雖然ESTIMATE參數(shù)提供給我們一種預(yù)估大小的功能，不過在使用EXPDP進行數(shù)據(jù)備份時，還是盡量不要使用，畢竟所有額外的操作都是有代價的。

Good luck.

secooler
10.04.10

-- The End --

本文名稱：【EXPDP】使用EXPDP備份數(shù)據(jù)時預(yù)估大小——ESTIMATE參數(shù)
文章分享：http://aaarwkj.com/article44/chcdhe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)網(wǎng)站建設(shè)、自適應(yīng)網(wǎng)站、網(wǎng)站制作、手機網(wǎng)站建設(shè)、微信公眾號、網(wǎng)站策劃

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)