安科网

Oracle 11g expdp中query参数的使用

lilening

lilening

2016-12-11

关注 关注

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

expdp oracle directory oracle数据库

lilening

lilening

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

Oracle和MySQL的数据导入为何差别这么大

经常会有一些朋友咨询我一些数据库的问题，我注意到一个很有意思的现象，凡是数据导入的问题，基本上都是Oracle类的，MySQL类的问题脑子里想了下竟然一次都没有。我们来梳理一下这个问题，分别从导出导入的方式来聊聊。导出有哪些工具呢，主要有exp，expdp

bfcady 2020-08-16

Centos610-oracle 备份和还原

本文是为基于Centos6.*系列的Oracle备份和还原的操作记录，其中根据expdp和impdp不同参数可实现不同场景下的导出导入，为不同OS下面的Oracle迁移打下基础。create user EXPDP_USER identified by 12

oraclemch 2020-02-21

Oracle impdp 导入报错 ORA-39083 + ORA-00439

xxx.dmp的expdp导出的源数据库是(企业版):Oracle Database 11g Enterprise Edition Release 11.2.0.1.0. 现在impdp导入的目标数据库是(标准版):Oracle Database 11g

LuoXinLoves 2020-01-04

liunx centos 下oracle expdp备份导出 sh脚本

dmpfile=bakexpdp$(date +%Y%m%d).dmplogfile=bakexpdp$(date +%Y%m%d).logexpdp user/ directory=data_dir dumpfile=$dmpfile full=y

zhangchaoming 2019-10-29

Linux下oracle用户crontab不生效的解决办法

现在DB迁移了，换到新的服务器下，想用oracle用户来完成这个操作，结果一开始测试的时候不成功，自动任务写好无法自动执行。问题分析从报错上看，linux报出了一个找不到expdp命令的错误，但是我们手动还能执行，这说明是不是shell里写的有些问题呢？e

程序方案代码 2019-06-21

Oracle数据库备份，exp和expdp的区别，imp和impdp的区别

expdp 用户名/密码@数据库实例名 directory=导出目录 schemas=哪些用户 dumpfile=导出的文件名.dmp logfile=导出的日志名.log. ————————————————————————————————————————

nikazhy 2018-11-16

Oracle跨平台数据迁移 expdp/impdp 字符集问题 导致ORA-02374

在源库执行导出操作之前，把qbjmes.project 字段改成超过103 。

oradbm 2018-05-05

利用Shell脚本实现将Oracle数据库的每日EXPDP导出文件复制到远程

于是我决定用Oracle数据库的逻辑备份工具EXPDP专门导出特定用户下的所有表的数据，并在每天凌晨4点通过LINUX操作系统的CRONTAB的计划任务定时执行来完成。这样，开发人员再有需要恢复某几张表的时候，直接用IMPDP工具导入某些表即可。这里，千万

尉微 2017-11-11

expdp network_link 命令备份远程主机上Oracle 11g数据库

运维部决定将数据库备份出来然后前去机房重启修改密码。　　保证两边登录的用户都有DBA权限，OK，cmd执行expdp命令，艹，报错!　　判断为版本不一致导致。配置好后能正常打开，继续新建DBA用户->新建database link 测试能正常访问。　

Hashxu 2017-07-29

Oracle数据泵expdp/impdp

和exp不同，在使用expdp导出数据时先要用DBA用户进入Oracle中执行一条指定备份路径后再授权可读写后，才能操作，具体操作步骤如下：。在导入的时候使用impdp导入，具体的使用方法同imp差不多，不过需要的是要指定备份目录，操作如下：。------

booloot 2017-07-02

Oracle 导出导入 expdp/impdp/exp/imp用法

4. DUMPFILE用于指定转储文件的名称,默认名称为expdat.dmpDUMPFILE=[directory_object:]file_name [,…

sticks00 2017-05-05

Oracle expdp按分区导出生成参数文件

from dba_tab_partitions where table_owner='TEST' and table_name='TEST';创建par文件，复制每一行内容至单独的par文件

花无涯 2017-03-23

Oracle 导出空表的可用方法

背景 使用Exp命令在Oracle 11g 以后不导出空表，是最近在工作中遇到一个很坑的问题，甚至已经被坑了不止一次，所以这次痛定思痛，准备把这个问题彻底解决。之所以叫新方法，那一定有老方法了，这个方法是一位博友很早就提出了，以下是原文，其实也说明了问题

xuy0 2016-08-03

利用Oracle自带的impdp和expdp进行简单备份

目前有个这样的需求，每天晚上23点将生产库的数据备份到备份机器上，第二天备份机器的数据库可以直接使用，数据是昨天生产库的数据。由于没有接触过高深的Oracle知识，只会简单的impdp和expdp这种自带的工具，所以打算用这种自带的命令加shell脚本进行

JaneChiu 2016-05-18

Oracle impdp的skip_constraint_errors选项跳过唯一约束错误

[oracle@ct6605 ~]$ ORACLE_SID=ct66[oracle@ct6605 ~]$ sqlplus / as sysdba. #建测试表SQL> create table scott.t_source as select * f

XMM00 2016-03-29

Oracle通过expdp的remap_data实现简单的数据脱敏

Oracle 11g开始,在impdp/expdp中提供remap_data参数,实现对导入或导出操作时转换数据.如果生产库数据要导出给测试库使用,并需要转换敏感数据,可以考虑通过remap_data参数去实现.[oracle@ct6605 ~]$ ORA

AnLeYe 2016-03-29

Oracle数据泵示例

expdp system/xxx DIRECTORY=dump_dir tableapace=xxx dumpfile=xxx_%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%".dmp logfile=xxx.log. exp

jhkj 2015-12-19

impdp报错ORA-39001、ORA-39000、ORA-31619

前不久使用数据泵工具impdp进行数据导入时，遇到报错，提示无效的dunp文件，通过查询文档才知道是由于因为从高版本到低版本不兼容导致的，下面分享一下解决方法。Import: Release 10.1.0.2.0 - Production on 星期二,

lcb0 2015-09-17

Oracle 11g数据泵详解

Oracle Database 10g引入了最新的数据泵技术，数据泵导出导入的作用。EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用。IMP只适用于EXP导出的文件，不适用于EXPDP导出文件；IMPDP只适用于

limiaoiao 2015-07-08

Oracle 11g版本EXPDP 的COMPRESSION参数压缩比堪比“gzip -9”

体验之，供参考。可见，11g中的COMPRESSION参数提供四个选项，分别是ALL、DATA_ONLY、METADATA_ONLY和NONE，非常的丰富，稍后我们将使用ALL参数进行操作。Export: Release 11.2.0.1.0 - Prod

MrDai 2015-04-29
lilening

lilening

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号