oracle 11g expdp工具的query参数使用
admin
2023-04-30 09:23:51
0次

实际生产中导数据时可能会需要导部分数据到测试库上，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, 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导出

zx@ORCL>select count(*) from e1 where id in( select id from e2 where birthday

  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\

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

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\

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

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

  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\

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

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 scn 2179047 where birthday

  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 scn 2179047  where birthday\

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

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

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


上一篇：盘点几款wepoker智能辅助插件,蜀山四川亲友房辅助器

下一篇：Oracle Java SE 任意代码执行漏洞加固

相关内容

热门资讯

空调连续工作多久需要停机休息 空调连续工作的时间取决于多个因素，包括空调的型号、功率、使用环境和维护情况等。一般来说，空调可以连续...
空调哪个符号是除湿的 空调除湿功能通常用一个水滴形状的符号表示。不同品牌和型号的空调遥控器可能会有所差异，但一般来说，除湿...
格力空调多长时间加一次氟利昂 1、一般情况下，空调对氟利昂的使用量是非常小的，所以在空调使用了四年左右的时候再考虑加也没问题。2、...
空调用多长时间需要加氟一次 一般情况下是五年。当出现以下这几种情况时，空调才需要加氟。1、空调的室内机和室外机各有两个接口，当接...
格调生活·C标思享荟：卡萨帝在... 9月12日的郑州龙湖金融岛，夜风轻拂，灯光璀璨。“C标时刻共传奇——卡萨帝思享荟暨20周年品牌盛典郑...
品牌价值破千亿、高端份额持续领... 9月12日，“C标时刻共传奇——卡萨帝思享荟暨20周年品牌盛典郑州站”在郑州龙湖金融岛举办。2026...
空调主板坏了修800还是换新的 空调主板坏了是修还是换新，需要考虑多个因素，以下是一些建议：1. 维修成本：如果维修费用较低，比如 ...
空调开多久需要休息多长时间 问题：空调开多久需要休息多长时间回答：1、在使用空调的过程中，一般空调开7~8个小时左右需要休息2~...
中央空调多长时间清洗一次 1、一般空调开机前清洗一次即可，空调开机中间时段再清洗一次，空调关机时清洗一次，这样比较合理。2、空...
中央空调氟利昂多久加一次 看空调制热和制冷程度，很多人的观点认为氟利昂必须一年放一次，其实这个观点是错误的，理解上面有点偏差，...