你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle 11g expdp中query参数的使用

[日期:2016-12-11] 来源:Linux社区  作者:DBA Fighting! [字体: ]

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

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款