共计 9600 个字符，预计需要花费 24 分钟才能阅读完成。
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
******************************************************************************
Dump file set for ZX.SYS_EXPORT_TABLE_01 is:
/home/oracle/e1.dmp
Job “ZX”.”SYS_EXPORT_TABLE_01″ successfully completed at Thu Jul 21 14:23:26 2016 elapsed 0 00:00:11
exit
查询 scn 号
linuxidc@ORCL>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
2179047
linuxidc@ORCL>select count(*) from e1;
COUNT(*)
———-
100
删除部分数据
linuxidc@ORCL>delete from e1 where id<20;
19 rows deleted.
linuxidc@ORCL>commit;
Commit complete.
linuxidc@ORCL>host
测试 query 及 flashback_scn
[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_1.dmp tables=zx.e1 query=zx.e1:\”where id\<=50\” flashback_scn=2179047
Export: Release 11.2.0.4.0 – Production on Thu Jul 21 14:25:41 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_1.dmp tables=zx.e1 query=zx.e1:”where id<=50″ flashback_scn=2179047
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
******************************************************************************
Dump file set for ZX.SYS_EXPORT_TABLE_01 is:
/home/oracle/e1_1.dmp
Job “ZX”.”SYS_EXPORT_TABLE_01″ successfully completed at Thu Jul 21 14:25:49 2016 elapsed 0 00:00:06
[oracle@rhel6 ~]$ exit
exit
测试复杂 query 导出
linuxidc@ORCL>select count(*) from e1 where id in(select id from e2 where birthday<sysdate);
COUNT(*)
———-
31
[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_2.dmp tables=zx.e1 query=zx.e1:\”where id in \(select id from e2 where birthday\<sysdate\)\”
Export: Release 11.2.0.4.0 – Production on Thu Jul 21 14:31:04 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_2.dmp tables=zx.e1 query=zx.e1:”where id in (select id from e2 where birthday<sysdate)”
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.242 KB 31 rows
Master table “ZX”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for ZX.SYS_EXPORT_TABLE_01 is:
/home/oracle/e1_2.dmp
Job “ZX”.”SYS_EXPORT_TABLE_01″ successfully completed at Thu Jul 21 14:31:12 2016 elapsed 0 00:00:06
[oracle@rhel6 ~]$ exit
exit
linuxidc@ORCL>host
测试复杂 query 及 flashback_scn 导出
[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_3.dmp tables=zx.e1 query=zx.e1:\”where id in \(select id from e2 where birthday\<sysdate\)\” flashback_scn=2179047
Export: Release 11.2.0.4.0 – Production on Thu Jul 21 14:32:07 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_3.dmp tables=zx.e1 query=zx.e1:”where id in (select id from e2 where birthday<sysdate)” flashback_scn=2179047
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
******************************************************************************
Dump file set for ZX.SYS_EXPORT_TABLE_01 is:
/home/oracle/e1_3.dmp
Job “ZX”.”SYS_EXPORT_TABLE_01″ successfully completed at Thu Jul 21 14:32:14 2016 elapsed 0 00:00:06
[oracle@rhel6 ~]$ exit
exit
删除 e2 部分数据
linuxidc@ORCL>delete from e2 where id>25 and id<30;
4 rows deleted.
linuxidc@ORCL>commit;
Commit complete.
linuxidc@ORCL>select count(*) from e1 where id in(select id from e2 where birthday<sysdate);
COUNT(*)
———-
27
测试 query 及 flashback_scn，结果只是对 e1 应用 flashback_snc，e2 没有应用
linuxidc@ORCL>host
[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_4.dmp tables=zx.e1 query=zx.e1:\”where id in \(select id from e2 where birthday\<sysdate\)\” flashback_scn=2179047
Export: Release 11.2.0.4.0 – Production on Thu Jul 21 14:33:55 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_4.dmp tables=zx.e1 query=zx.e1:”where id in (select id from e2 where birthday<sysdate)” flashback_scn=2179047
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.648 KB 46 rows
Master table “ZX”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for ZX.SYS_EXPORT_TABLE_01 is:
/home/oracle/e1_4.dmp
Job “ZX”.”SYS_EXPORT_TABLE_01″ successfully completed at Thu Jul 21 14:34:03 2016 elapsed 0 00:00:06
[oracle@rhel6 ~]$ exit
exit
使 e1 和 e2 都应用 flashback_scn
linuxidc@ORCL>select count(*) from e1 where id in(select id from e2 as of scn 2179047 where birthday<sysdate);
COUNT(*)
———-
31
linuxidc@ORCL>host
[oracle@rhel6 ~]$ expdp zx/zx directory=dir dumpfile=e1_5.dmp tables=zx.e1 query=zx.e1:\”where id in \(select id from e2 as of scn 2179047 where birthday\<sysdate\)\” flashback_scn=2179047
Export: Release 11.2.0.4.0 – Production on Thu Jul 21 14:39:52 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_5.dmp tables=zx.e1 query=zx.e1:”where id in (select id from e2 as of scn2179047 where birthday<sysdate)” flashback_scn=2179047
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
******************************************************************************
多个表使用 query 条件则使用 ’,’ 分开
[oracle@rhel6 ~]$ expdp system/123456 directory=dump dumpfile=query.dmp tables=zx.abc,zx.abce query=zx.abc:\”where id \< 4\”,zx.abce:\”where id \< 4\”
Export: Release 11.2.0.4.0 – Production on Fri Dec 9 16:13:41 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
FLASHBACK automatically enabled to preserve database integrity.
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: system/******** directory=dump dumpfile=query.dmp tables=zx.abc,zx.abce query=zx.abc:”where id < 4″,zx.abce:”where id < 4″
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “ZX”.”ABC” 5.898 KB 2 rows
. . exported “ZX”.”ABCE” 5.898 KB 2 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/query.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at Fri Dec 9 16:14:04 2016 elapsed 0 00:00:19
更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-12/138213.htm
