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.