小叶同学用exp导出数据，加了query参数，结果命令执行报ORA-00904错误
如果query在命令行直接指定的话，所有的特殊字符都需要做转义的，如果是放到参数文件里，就无需转义了
简单的做个测试：
[oracle@goolen goolen]$ exp userid=scott/tiger tables=emp file=./a.dmp query=\'where ename='SCOTT'\'
Export: Release 10.2.0.4.0 - Production on Tue Dec 23 11:54:47 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table EMP
EXP-00056: ORACLE error 904 encountered
ORA-00904: "SCOTT": invalid identifier
Export terminated successfully with warnings.
[oracle@goolen goolen]$
转义后可以成功导出：
[oracle@goolen goolen]$ exp userid=scott/tiger tables=emp file=./b.dmp query=\"where\ ename\=\'SCOTT\'\" STATISTICS=none
Export: Release 10.2.0.4.0 - Production on Tue Dec 23 11:55:45 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table EMP 1 rows exported
Export terminated successfully without warnings.
把query加到parfile里：
[oracle@goolen goolen]$ cat parfile
query="where ename='SCOTT'"
[oracle@goolen goolen]$ exp userid=scott/tiger tables=emp file=./c.dmp parfile=./parfile STATISTICS=none
Export: Release 10.2.0.4.0 - Production on Tue Dec 23 11:57:48 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table EMP 1 rows exported
Export terminated successfully without warnings.
如果query在命令行直接指定的话，所有的特殊字符都需要做转义的，如果是放到参数文件里，就无需转义了
简单的做个测试：
[oracle@goolen goolen]$ exp userid=scott/tiger tables=emp file=./a.dmp query=\'where ename='SCOTT'\'
Export: Release 10.2.0.4.0 - Production on Tue Dec 23 11:54:47 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table EMP
EXP-00056: ORACLE error 904 encountered
ORA-00904: "SCOTT": invalid identifier
Export terminated successfully with warnings.
[oracle@goolen goolen]$
转义后可以成功导出：
[oracle@goolen goolen]$ exp userid=scott/tiger tables=emp file=./b.dmp query=\"where\ ename\=\'SCOTT\'\" STATISTICS=none
Export: Release 10.2.0.4.0 - Production on Tue Dec 23 11:55:45 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table EMP 1 rows exported
Export terminated successfully without warnings.
把query加到parfile里：
[oracle@goolen goolen]$ cat parfile
query="where ename='SCOTT'"
[oracle@goolen goolen]$ exp userid=scott/tiger tables=emp file=./c.dmp parfile=./parfile STATISTICS=none
Export: Release 10.2.0.4.0 - Production on Tue Dec 23 11:57:48 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table EMP 1 rows exported
Export terminated successfully without warnings.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1377028/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/23249684/viewspace-1377028/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
