ITPub博客

首页 > 数据库 > Oracle > [20180224]expdp query 写法问题.txt

[20180224]expdp query 写法问题.txt

原创 Oracle 作者:lfree 时间:2018-02-24 09:04:42 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2151192/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2416
  • 访问量
    6185917