[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支持这些参数内容的显示.
