【EXPDP】使用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引數的描述
參考連結:
引用內容如下:
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 --
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/519536/viewspace-631804/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【EXPDP】不使用DIRECTORY引數完成expdp資料備份
- 【EXPDP】使用expdp的QUERY引數限定備份資料的範圍
- 【Data Pump】理解expdp中的ESTIMATE和ESTIMATE_ONLY引數
- expdp在匯出時對資料大小進行評估
- oracle資料泵備份(Expdp命令)Oracle
- 【EXPDP】運用PARALLEL 和FILESIZE引數提高備份效率Parallel
- 【EXPDP】使用EXPDP備份資料時ORA-39125、ORA-04031錯誤原因分析與排查
- expdp/impdp 使用version引數跨版本資料遷移
- 使用expdp匯出時評估所需儲存容量大小
- 使用crontab和expdp實現資料庫定期邏輯備份資料庫
- EXPDP的parallel引數Parallel
- Oracle資料庫的邏輯備份工具-expdp資料泵Oracle資料庫
- windowns系統,oracle資料庫expdp自動備份Oracle資料庫
- Oracle 邏輯備份 expdp/impdpOracle
- expdp 邏輯備份指令碼指令碼
- Oracle expdp 排除表引數Oracle
- 揭祕ORACLE備份之--邏輯備份(EXPDP)Oracle
- expdp備份+FTP自動上傳FTP
- Oracle expdp/impdp匯出匯入命令及資料庫備份Oracle資料庫
- 監控資料備份恢復完成進度(EXPDP/IMPDP/RMAN)
- Oracle expdp/impdp匯入匯出備份資料庫指令碼Oracle資料庫指令碼
- 【Toad】使用Toad呼叫expdp資料泵備份資料步驟及注意事項
- EXPDP資料泵使用方法
- Oracle 邏輯備份之EXPDP精講Oracle
- [重慶思莊每日技術分享]-expdp導資料時評估匯出檔案大小
- Linux 平臺下 Oracle 資料泵備份(expdp) SHELL 指令碼LinuxOracle指令碼
- Oracle expdp impdp dump引數介紹Oracle
- [EXPDP]使用11g的資料泵實現對邏輯備份資料進行加密加密
- 【EXPDP】使用11g的資料泵實現對邏輯備份資料進行加密加密
- expdp 使用QUERY 匯出部分資料。
- 使用expdp、impdp遷移資料庫資料庫
- Expdp 備份到ASM之 ORA-39070ASM
- Linux或UNIX系統下oracle資料庫expdp自動備份LinuxOracle資料庫
- 使用資料泵工具expdp工具匯出資料
- expdp impdp Data Pump(資料泵)使用解析
- EXPDP 和 IMPDP 資料泵的使用_1
- EXPDP 和 IMPDP 資料泵的使用_2
- 資料泵 EXPDP 匯出工具的使用