ITPub博客

首页 > 数据库 > 数据库开发技术 > 关于使用exp按照条件导出数据的问题(摘)

关于使用exp按照条件导出数据的问题(摘)

原创 数据库开发技术 作者:realji 时间:2008-05-27 16:45:13 0 删除 编辑

http://www.heysky.net/archives/2007/09/exp_query_format.html

昨天需要按照条件导出一个表,再导入另一个数据库,很自然想到了 exp 的 query 参数,本来以为可以很顺利完成,结果 query 参数的格式怎么也写不对,因为时间紧迫,最后通过 dblink 直接插入了另一个数据库。今天回过头来再研究了一下,发现其实也不复杂,按照文档还是能很好的解决的。

希望导出测试表中日期为 2007-09-22 的一条数据:

SQL> select * from skytest;

SID DT
---------- -----------------
1 20070913 00:00:00
2 20070914 00:00:00
3 20070915 00:00:00
4 20070916 00:00:00
5 20070917 00:00:00
6 20070918 00:00:00
7 20070919 00:00:00
8 20070920 00:00:00
9 20070921 00:00:00
10 20070922 00:00:00
12 20070924 00:00:00

11 rows selected.

凭印象按照以下格式运行:

exp / tables=skytest file=test.dmp query="where dt=to_date('2007-09-22','yyyy-mm-dd')"
结果报错:
ksh: syntax error: `(' unexpected

其实文档中讲得很清楚:

exp scott/tiger TABLES=emp QUERY="WHERE job='SALESMAN' and sal <1600"
Note:
Because the value of the QUERY parameter contains blanks, most operating systems require that the entire strings WHERE job='SALESMAN' and sal<1600 be placed in double quotation marks or marked as a literal by some method. Operating system reserved characters also need to be preceded by an escape character. See your Oracle operating system-specific documentation for information about special and reserved characters on your system.

所有操作系统保留字符都要使用转义符号,看来 ( ) 也需要转义。

正确的写法:

oracle DBALNP01 > exp / tables=skytest file=test.dmp query="where dt=to_date('2007-09-22','yyyy-mm-dd')"

Export: Release 8.1.7.4.0 - Production on Wed Sep 12 04:30:45 2007

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table SKYTEST 1 rows exported
Export terminated successfully without warnings.

简洁的写法:
如果环境变量设置合适,就不需要使用 to_date 函数了

oracle DBALNP01 > exp / tables=skytest file=test.dmp query="where dt='20070922'"

Export: Release 8.1.7.4.0 - Production on Wed Sep 12 04:25:56 2007

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table SKYTEST 1 rows exported
Export terminated successfully without warnings.

任何 os 平台都适用的方法(推荐):
以上两种方法并不一定适合其他操作系统,不过使用 parfile 就不用担心这些格式问题

oracle DBALNP01 > cat > test.par
tables=skytest
file=test.dmp
query="where dt=to_date('2007-09-22','yyyy-mm-dd')"

oracle DBALNP01 > exp / parfile=test.par

Export: Release 8.1.7.4.0 - Production on Wed Sep 12 04:22:27 2007

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table SKYTEST 1 rows exported
Export terminated successfully without warnings.

[@more@]

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

全部评论

注册时间:2008-01-09

  • 博文量
    55
  • 访问量
    151670