ANBOB™

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.

打赏

微信扫一扫，打赏作者吧～