oracle Expdp带条件,Oracle 11g expdp中query参数的使用

Oracle 11g expdp中提供了query参数，可以在需要按条件导出表中部分数据时使用，它的使用就像是在select语句中的where条件使用一样。

数据库版本

linuxidc@ORCL>select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE11.2.0.4.0Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

创建测试表

linuxidc@ORCL>create table e1 (id number,name varchar2(20));

Table created.

linuxidc@ORCL>create table e2 (id number,birthday date);

Table created.

插入测试数据

linuxidc@ORCL>insert into e1 select level,lpad(level,20,'*') from dual connect by level <= 100;

100 rows created.

linuxidc@ORCL>commit;

Commit complete.

linuxidc@ORCL>insert into e2 select level,sysdate-50+level from dual connect by level <= 100;

100 rows created.

linuxidc@ORCL>commit;

Commit complete.

创建目录

linuxidc@ORCL>create directory dir as '/home/oracle/';

Directory created.

linuxidc@ORCL>host

测试使用query导出

注意：如果query条件在parfile中则不需要用'\'进行转义

[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1.dmp tables=zx.e1 query=zx.e1:\"where id<=50\"

bash: =50": No such file or directory

Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:23:11 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "ZX"."SYS_EXPORT_TABLE_01":  zx/******** directory=dir dumpfile=e1.dmp tables=zx.e1 query=zx.e1:"where id<=50"

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "ZX"."E1"                                  6.757 KB      50 rows

Master table "ZX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

***************************************************************************