Use of query in Data Pump expdp and precautions

When using a data pump to export data, sometimes we need to filter out some data that does not need to be exported according to the conditions. In this case, we need to use the parameter query. The following is a small example of simple query. In this example, we can also see a defect in the Data Pump, that is, the query parameter conflicts with the estimate parameter, when we use the query parameter, the Data Pump estimate data volume will ignore the filtering of the query parameter, so that the estimate value is abnormal.

 

 

Expdp systemparfile = bi_20131203.par

Export: Release11.2.0.3.0-Production on Tue Dec 3 18:51:04 2013

Copyright (c) 1982,2011, Oracle and/or its affiliates. Allrights reserved.

Password:

Connected to: OracleDatabase 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With thePartitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and RealApplication Testing options

Starting "SYSTEM". "SYS_EXPORT_TABLE_01": system/******** parfile = bi_20131203.par

Estimate in progressusing BLOCKS method...

Processing objecttype TABLE_EXPORT/TABLE/TABLE_DATA

Total estimationusing BLOCKS method: 61.75 GB

Processing objecttype TABLE_EXPORT/TABLE

Processing objecttype TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

 

 

View the parameter file more bi_20131203.par.

 

Directory = dump_bi

Tables = bi_dw.DW_MID_CONTRACT

Cluster = n filesize = 4G

Dumpfile = expdp_bi_ods_tables_20131203 _ % U. dmp

Query = bi_dw.DW_MID_CONTRACT: "where data_date in (to_date ('2017-11-13 ', 'yyyy-mm-dd'), to_date ('2017-11-14 ', 'yyyy-mm-dd'), to_date ('2017-8-13 ', 'yyyy-mm-dd'), to_date ('2017-8-14 ', 'yyyy-mm-dd '))"

Nologfile = y

 

 

The database shows that the DW_MID_CONTRACT table has 0.16 billion of the data size and the segment of the table reaches 60 GB. However, the filtered data is only 2639086 rows, but the value calculated by expdp

Total estimationusing BLOCKS method: 61.75 GB

 

The exported file is only 873 M expdp_bi_ods_tables_20131203_01.dmp.

 

In addition, we can execute expdp system ATTACH = SYS_EXPORT_TABLE_01 to import data to this task.

The job name can be specified by the job parameter or by default. dba_datapump_jobs records the ongoing data pump task.

Select * from dba_datapump_jobs;

SYS_EXPORT_TABLE_01

GoldenGate performs two-way Oracle-Oracle replication without using a Data Pump

One-way Oracle-Oracle Replication Using the GoldenGate Data Pump

How to debug Oracle Data Pump (expdp/impdp)

Oracle Database Export data pump (EXPDP) file storage location

Export of Oracle 10g Data Pump Partition Table