oracle 11g expdp工具的query参数使用
|
实际生产中导数据时可能会需要导部分数据到测试库上，expdp的query参数就可以完成这样的需求。 数据库版本 zx@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 创建测试表 zx@ORCL>create table e1 (id number,name varchar2(20)); Table created. zx@ORCL>create table e2 (id number,birthday date); Table created. 插入测试数据 zx@ORCL>insert into e1 select level,lpad(level,20,'*') from dual connect by level <= 100; 100 rows created. zx@ORCL>commit; Commit complete. zx@ORCL>insert into e2 select level,sysdate-50+level from dual connect by level <= 100; 100 rows created. zx@ORCL>commit; Commit complete. 创建目录 zx@ORCL>create directory dir as '/home/oracle/'; Directory created. zx@ORCL>host 测试使用query导出 [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号 zx@ORCL>select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 2179047 zx@ORCL>select count(*) from e1; COUNT(*) ---------- 100 删除部分数据 zx@ORCL>delete from e1 where id<20; 19 rows deleted. zx@ORCL>commit; Commit complete. zx@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,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导出 zx@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,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 zx@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,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部分数据 zx@ORCL>delete from e2 where id>25 and id<30; 4 rows deleted. zx@ORCL>commit; Commit complete. zx@ORCL>select count(*) from e1 where id in( select id from e2 where birthday<sysdate); COUNT(*) ---------- 27 测试query及flashback_scn，结果只是对e1应用flashback_snc，e2没有应用 zx@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,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 zx@ORCL>select count(*) from e1 where id in( select id from e2 as of scn2179047where birthday<sysdate); COUNT(*) ---------- 31 zx@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 scn2179047where birthday&;sysdate)" flashback_scn=2179047 Export: Release 11.2.0.4.0 - Production on Thu Jul 21 14:39:52 2016 Copyright (c) 1982,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 scn2179047where 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 ****************************************************************************** （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |