oracle导出where,exp query 导出加where条件

exp 导出单表时可以加where条件，但转义有时很头疼，下面请看我的实验

SQL> create table test_exp(id number,in_date date);

Table created.

SQL> begin

2  for i in 1..100 loop

3  insert into test_exp(id,in_date) values(i,sysdate-i);

4  end loop;

5  end;

6  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from test_exp where rownum<10;

ID IN_DATE

---------- --------------

1 23-5月 -11

2 22-5月 -11

3 21-5月 -11

4 20-5月 -11

5 19-5月 -11

6 18-5月 -11

7 17-5月 -11

8 16-5月 -11

9 15-5月 -11

9 rows selected.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select * from test_exp where rownum<10;

ID IN_DATE

---------- -------------------

1 2011-05-23 15:57:03

2 2011-05-22 15:57:03

3 2011-05-21 15:57:03

4 2011-05-20 15:57:03

5 2011-05-19 15:57:03

6 2011-05-18 15:57:03

7 2011-05-17 15:57:03

8 2011-05-16 15:57:03

9 2011-05-15 15:57:03

9 rows selected.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[oracle@orazhang ~]$ exp anbob/anbob file=test_exp tables=test_exp query="where in_date>to_date('2011-5-20','yyyy-mm-dd')"

LRM-00116: syntax error at ')' following 'yyyy-mm-dd'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help

EXP-00000: Export terminated unsuccessfully

[oracle@orazhang ~]$ exp anbob/anbob file=test_exp tables=test_exp query="where in_date>to_date\\('2011-5-20','yyyy-mm-dd'\\)"

LRM-00112: multiple values not allowed for parameter 'query'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help

EXP-00000: Export terminated unsuccessfully

[oracle@orazhang ~]$ exp anbob/anbob file=test_exp tables=test_exp query="where in_date>to_date\\(\\'2011-5-20\\'\\,\\'yyyy-mm-dd\\'\\)"

LRM-00112: multiple values not allowed for parameter 'query'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help

EXP-00000: Export terminated unsuccessfully

[oracle@orazhang ~]$ exp anbob/anbob file=test_exp tables=test_exp query="where in_date>to_date\\(\\'2011-5-20\\'\\,\\'yyyy-mm-dd\\'\\)"

[oracle@orazhang ~]$ exp anbob/anbob file=test_exp tables=test_exp query=\\"where in_date>to_date\\(\\'2011-5-20\\'\\,\\'yyyy-mm-dd\\'\\)\\"

LRM-00111: no closing quote for value 'where in_d'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help

EXP-00000: Export terminated unsuccessfully

[oracle@orazhang ~]$ exp anbob/anbob file=test_exp tables=test_exp query=\\"where in_date\\>to_date\\(\\'2011-5-20\\'\\,\\'yyyy-mm-dd\\'\\)\\"

Export: Release 10.2.0.1.0 - Production on 星期二 5月 24 16:06:23 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                       TEST_EXP          4 rows exported

Export terminated successfully without warnings.

[oracle@orazhang ~]$

---windows xp-------------

C:\\>exp anbob/anbob@mytest tables=test_exp query=\\"where in_date>to_date('2011-5-20','yyyy-mm-dd')\\"

Export: Release 10.1.0.2.0 - Production on 星期二 5月 24 16:07:54 2011

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                       TEST_EXP          4 rows exported

Export terminated successfully without warnings.