Shulou(Shulou.com)06/02 Report--

When using EXPDP to complete the data export, you can use the ESTIMATE parameter to evaluate the size of the database object to be exported, which is briefly demonstrated for reference.

1. View help information about ESTIMATE parameters

1) View command line help information

Ora10g@secDB / expdp$ expdp help=y

……

ESTIMATE Calculate job estimates where the valid keywords are:

(BLOCKS) and STATISTICS.

……

2) description of ESTIMATE parameters in the official Oracle documentation

Reference link: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#sthref105

The references are as follows:

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

two。 Explicitly specify the ESTIMATE parameter as 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

At this time, the estimation unit is counted according to the database block, which is not very accurate compared to the final derived size, but it has a certain reference significance.

3. Specify the ESTIMATE parameter as 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

What is the reason for the outrageous statistical results at this time?

The real reason is that when using ESTIMATE=STATISTICS parameter estimation, the data source is the analysis data of the table, which will result in data distortion here if the table has not been analyzed or the analysis data is inaccurate.

4. Analyze the sec users in the database, and then export the data again.

1) analyze sec users

Sys@ora10g > exec dbms_stats.gather_schema_stats (OWNNAME= > 'SEC',ESTIMATE_PERCENT= > 10jie DEGREE = > 4meme cascade = > true)

PL/SQL procedure successfully completed.

2) Export the data under the sec user again

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, the estimated data is close to the size of the last generated data.

5. Do not specify ESTIMATE parameter

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

When the ESTIMATE parameter is not specified, BLOCKS is used by default for estimation. However, the estimation of specific database objects is omitted, and only the following information prompts:

Total estimation using BLOCKS method: 6 MB

6. Summary

Although the ESTIMATE parameter provides us with the ability to estimate the size, we should try not to use it when using EXPDP for data backup. After all, all the extra operations come at a cost.

Good luck.

Secooler

10.04.10

-- The End--

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.