How to use query in EXPDP

在datapump export中Oracle提供了query参数,允许只导出满足query条件的记录。

在Oracle 10gR2的官方文档中,提到的语法是:

QUERY = [schema.][table_name:] query_clause

并且给出了例子:

QUERY=employees:'”WHERE department_id > 10 AND salary > 10000″‘

但是实际上这个例子是错误的,按照例子中的语法,则会碰到ORA-06502错误。

D:\Temp>type emp_query.par
QUERY=emp:'"WHERE rownum<4"'
NOLOGFILE=y
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=exp1.dmp

D:\Temp>expdp scott/tiger parfile=emp_query.par

Export: Release 11.1.0.6.0 - Production on Thursday, 26 February, 2009 18:07:24

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Oracle Database Vault
and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** parfile=emp_query.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLSetTextVar in routine kuxslSetParam:
LPX-314: an internal failure occurred
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  D:\ORACLE\ADMIN\ORCL11G\DPDUMP\EXP1.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 18:08:04

这份文档错误在Oracle 11gR1的官方文档中已经修正了。正确的语法,是去除掉例子中query字串的前后单引号:QUERY=emp:”WHERE rownum<4"

D:\Temp>type emp_query.par
QUERY=emp:"WHERE rownum<4"
NOLOGFILE=y
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=exp1.dmp

D:\Temp>expdp scott/tiger parfile=emp_query.par reuse_dumpfiles=y

Export: Release 11.1.0.6.0 - Production on Thursday, 26 February, 2009 18:16:59

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Oracle Database Vault
and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** parfile=emp_query.par reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.125 KB       3 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  D:\ORACLE\ADMIN\ORCL11G\DPDUMP\EXP1.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:17:32

更详细的Query用法可以参看Metalink Note:277010.1。

5 thoughts on “How to use query in EXPDP

  1. 如果写在参数文件中是您这样的效果。
    如果是在一条语句那样的执行就需要加单引号了,不然会报错的。

    您可以再做一个小小的测试。

  2. Pingback: dept consolidation

Leave a Reply

Your email address will not be published. Required fields are marked *