当前位置： 首页 > news >正文

[20180224]expdp query 写法问题.txt

news 来源：原创 2024/4/20 1:35:42

[20180224]expdp query 写法问题.txt

--//如果使用expdp/impdp导入导出,如果参数复杂,最好的建议使用参数文件.避免各种问题.通过简单的例子说明问题.

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试:
--//假设仅仅导出表emp sal<=2000记录:

$ expdp scott/book DUMPFILE=emp.dp  tables=emp query=emp:"where sal<2000"
Export: Release 11.2.0.4.0 - Production on Sat Feb 24 08:40:27 2018
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 "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a* DUMPFILE=emp.dp tables=emp query=emp:where sal<2000
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
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
ORA-00936: missing expression
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/book/dpdump/emp.dp
Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Sat Feb 24 08:40:36 2018 elapsed 0 00:00:09

--//注意看下线线,实际上导出报错.
$ rm /u01/app/oracle/admin/book/dpdump/emp.dp
/bin/rm: remove regular file `/u01/app/oracle/admin/book/dpdump/emp.dp'? y

--//在where条件加入单引号.

$ expdp scott/book DUMPFILE=emp.dp  tables=emp query=emp:'"where sal<2000"'
Export: Release 11.2.0.4.0 - Production on Sat Feb 24 08:41:54 2018
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 "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a*** DUMPFILE=emp.dp tables=emp query=emp:"where sal<2000"
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
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.320 KB       8 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/book/dpdump/emp.dp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Feb 24 08:42:04 2018 elapsed 0 00:00:09

--//实际上对于linux bash要转义"以及<.不转义<,报错.
$ expdp scott/book DUMPFILE=emp.dp  tables=emp query=emp:\"where sal<2000\"
-bash: 2000": No such file or directory

--//要写成如下":

$ expdp scott/book DUMPFILE=emp.dp  tables=emp query=emp:\"where sal\<2000\"
Export: Release 11.2.0.4.0 - Production on Sat Feb 24 08:50:46 2018
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 "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a** DUMPFILE=emp.dp tables=emp query=emp:"where sal<2000"
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
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.320 KB       8 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/book/dpdump/emp.dp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Feb 24 08:50:56 2018 elapsed 0 00:00:09


--//OK成功.如果要导出 job='SALESMAN'的记录更加麻烦.还要转义里面的单引号.

$ rm /u01/app/oracle/admin/book/dpdump/emp.dp

$ expdp scott/book DUMPFILE=emp.dp  tables=emp query=emp:\"where job=\'SALESMAN\'\"
Export: Release 11.2.0.4.0 - Production on Sat Feb 24 08:53:44 2018
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 "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a*** DUMPFILE=emp.dp tables=emp query=emp:"where job='SALESMAN'"
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
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.171 KB       4 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/book/dpdump/emp.dp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Feb 24 08:53:54 2018 elapsed 0 00:00:09


3.可以看出以上命令的复杂性,遇到这种情况最佳的方式建立使用参数文件:

$ cat q.par
tables=emp
DUMPFILE=emp.dp
query=emp:"where sal<2000 and job='SALESMAN'"

$ expdp scott/book PARFILE=q.par
Export: Release 11.2.0.4.0 - Production on Sat Feb 24 09:01:37 2018
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 "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a**** PARFILE=q.par
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
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.171 KB       4 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/book/dpdump/emp.dp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Feb 24 09:01:48 2018 elapsed 0 00:00:09

总之:
遇到这种写法特殊的expdp/impdp导入导出,最佳的方式就是使用参数文件.
缺点就是不显示参数文件的内容,好像12c支持这些参数内容的显示.

相关文章：

  • Android SDK Manager 和 AVD Manager使用
  • Ubuntu 开机挂载分区
  • SQL自动审核工具archer
  • django生产环节部署
  • 设计模式 代理模式（Proxy）
  • Python GIL(Global Interpreter Lock)
  • blend4web的三维小场景的开发应用（2）
  • 前端面试题整理汇总
  • 如何优雅的在手机上进行Python编程
  • TiDB 源码阅读系列文章（一）序
  • 小程序开发 Wepy 框架 使用规范
  • 傲娇的性格、逗比的语气，和聊天机器人谈情说爱可行吗？
  • 自己简单写的 事件订阅机制
  • ReactNative从零到完整项目-state使用详解
  • 150行实现Promise 90%的功能
  • [NodeJS] 关于Buffer
  • 2017 年终总结 —— 在路上
  • Android 初级面试者拾遗（前台界面篇）之 Activity 和 Fragment
  • create-react-app项目添加less配置
  • css属性的继承、初识值、计算值、当前值、应用值
  • export和import的用法总结
  • go append函数以及写入
  • Java IO学习笔记一
  • js写一个简单的选项卡
  • Laravel 菜鸟晋级之路
  • maven工程打包jar以及java jar命令的classpath使用
  • MobX
  • PHP的Ev教程三(Periodic watcher)
  • Python代码面试必读 - Data Structures and Algorithms in Python
  • sessionStorage和localStorage
  • Three.js 再探 - 写一个跳一跳极简版游戏
  • V4L2视频输入框架概述
  • -- 查询加强-- 使用如何where子句进行筛选，% _ like的使用
  • 大快搜索数据爬虫技术实例安装教学篇
  • 如何利用MongoDB打造TOP榜小程序
  • 数据仓库的几种建模方法
  • 跳前端坑前，先看看这个！！
  • 用mpvue开发微信小程序
  • No resource identifier found for attribute,RxJava之zip操作符
  • 2017年360最后一道编程题
  • !!【OpenCV学习】计算两幅图像的重叠区域
  • （2）MFC+openGL单文档框架glFrame
  • (Ruby)Ubuntu12.04安装Rails环境
  • （介绍与使用）物联网NodeMCUESP8266（ESP-12F）连接新版onenet mqtt协议实现上传数据（温湿度）和下发指令（控制LED灯）
  • （四）Linux Shell编程——输入输出重定向
  • （完整代码）R语言中利用SVM-RFE机器学习算法筛选关键因子
  • (一)基于IDEA的JAVA基础1
  • (转)详解PHP处理密码的几种方式
  • .gitignore文件_Git：.gitignore
  • .NET/C# 在代码中测量代码执行耗时的建议（比较系统性能计数器和系统时间）
  • .pop ----remove 删除
  • @CacheInvalidate(name = “xxx“, key = “#results.![a+b]“,multi = true)是什么意思
  • @DependsOn：解析 Spring 中的依赖关系之艺术
  • @entity 不限字节长度的类型_一文读懂Redis常见对象类型的底层数据结构
  • @RequestParam详解