Summary on the usage of query in expdp
Today, I saw that someone in the group asked about the solution of using the query parameter to report errors in exporting data using expdp. I also messed around out of curiosity, which is recorded as follows.
1. The first time I tried
[oracle@DB ~] $expdp scott/scott tables=emp1 dumpfile=emp1.dmp logfile=emp1.log query=emp1: "where rownum
< 5" Export: Release 11.2.0.4.0 - Production on 星期日 6月 18 01:06:05 2017 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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORA-39001: 参数值无效ORA-39035: 已经指定了数据过滤器 SUBQUERY。 ORA-39001: 参数值无效ORA-39035: 已经指定了数据过滤器 SUBQUERY。 上述错误说明query语法写的有问题 正确写法要用\转义引号 于是再次编写了一下，执行，OK！ [oracle@DB ~]$ expdp scott/scott tables=emp1 dumpfile=emp1.dmp logfile=emp1.log query=\"where rownum \< 5\" Export: Release 11.2.0.4.0 - Production on 星期日 6月 18 01:18:52 2017 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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options启动 "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** tables=emp1 dumpfile=emp1.dmp logfile=emp1.log query="where rownum < 5" 正在使用 BLOCKS 方法进行估计...处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA使用 BLOCKS 方法的总估计: 64 KB处理对象类型 TABLE_EXPORT/TABLE/TABLE. . 导出了 "SCOTT"."EMP1" 8.179 KB 4 行已成功加载/卸载了主表 "SCOTT"."SYS_EXPORT_TABLE_01" ******************************************************************************SCOTT.SYS_EXPORT_TABLE_01 的转储文件集为: /u01/app/oracle/admin/orcl/dpdump/emp1.dmp作业 "SCOTT"."SYS_EXPORT_TABLE_01" 已于 星期日 6月 18 01:19:03 2017 elapsed 0 00:00:10 成功完成 通过上面截图可以看到：1中双引号和小于号前面都要加上反斜线\转义，在实际oracle中会把这些反斜线去掉来执行，注意观察2处。 当然结果是OK的，把原表备份一下然后删除，导入验证一下，如下： 2.答主突发奇想又测试了一种情况，就是当query条件中有大于号的情况。如下： [oracle@DB ~]$ expdp scott/scott tables=emp1 dumpfile=emp1_2.dmp logfile=emp1.log query=\"where hiredate >To_date\ (\ '1982-01-02\',\ 'yyyy/mm/dd\'\)\ "- bash: to_date ('1982 Universe 01company02lemyyyhand)": No such file or directory
See the result? if there is a greater than sign in the condition without a backslash, the system defaults the greater than sign to the redefinition symbol. This is as follows:
Change it, execute it again, and OK it. Children's shoes, please focus on the 1 or 2 places marked in the picture below.
[oracle@DB ~] $expdp scott/scott tables=emp1 dumpfile=emp1_2.dmp logfile=emp1.log query=\ "where hiredate\ > to_date\ (\ '1982-01-02\',\ 'yyyy/mm/dd\'\)\" Export: Release 11.2.0.4.0-Production on Sunday June 18 01:59:56 2017 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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options launches "SCOTT". "SYS_EXPORT_TABLE_01": scott/* tables=emp1 dumpfile=emp1_2.dmp logfile=emp1.log query= "where hiredate > to_date" is using the BLOCKS method to estimate. Total estimate of processing object type TABLE_EXPORT/TABLE/TABLE_DATA using BLOCKS method: 64 KB processing object type TABLE_EXPORT/TABLE/TABLE. . Exported "SCOTT". "EMP1" 8.125 KB 3 lines have successfully loaded / unloaded the main table "SCOTT". "SYS_EXPORT_TABLE_01" * * * the dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: / u01/app/oracle/admin/orcl/dpdump/emp1_2.dmp job "SCOTT". "SYS_EXPORT_TABLE_01" was successfully completed on Sunday June 18 02:00:01 2017 elapsed 0 00:00:04
3. If there are children's shoes who want to export multiple tables at once, they need to be defined as follows: expdp scott/scott tables=emp1,emp2 dumpfile=emp.dmp logfile=emp1.log query=emp1:\ "where rownum
