oracle 如何在expdp中使用QUERY只提取最近3个月的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33161901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use QUERY in expdp to extract only the last 3 months data
提问by Joie Tamayo
In my other thread, I have asked how to extract only the last 3 months. However, I managed to extract data from the last 3 months only from one table and it extracted all data for other tables. I have several tables in my schema and the column names for the timestamp are different.
在我的另一个线程中，我询问了如何仅提取最近 3 个月的数据。但是，我设法仅从一张表中提取了过去 3 个月的数据，并提取了其他表的所有数据。我的架构中有几个表，时间戳的列名不同。
In my par file I have the following QUERY but it did not work. I got ORA-00911 error message. I would like to know if the syntax of the below query is correct/possible .
在我的 par 文件中，我有以下 QUERY 但它不起作用。我收到 ORA-00911 错误消息。我想知道以下查询的语法是否正确/可能。
QUERY=TABLE1,TABLE2:"where TABLE1_STARTTIME >= TO_DATE('01-AUG-2015','dd-mon-yyyy') and TABLE2_STARTIME >= TO_DATE('01-AUG-2015','dd-mon-yyyy');"
回答by Lalit Kumar B
QUERY=TABLE1,TABLE2:"where ... TO_DATE('01-AUG-2015','dd-mon-yyyy');"
QUERY=TABLE1,TABLE2:"where ... TO_DATE('01-AUG-2015','dd-mon-yyyy');"
Removethe semi-colon in the QUERYparameter.:
删除QUERY参数中的分号。：
QUERY=TABLE1,TABLE2:"where TABLE1_STARTTIME >= TO_DATE('01-AUG-2015','dd-mon-yyyy')
and TABLE2_STARTIME >= TO_DATE('01-AUG-2015','dd-mon-yyyy')"
On a side note:
附带说明：
Not directly related to your issue. But remember TO_DATEis NLS dependent. You should specify the NLS_DATE_LANGUAGE, else your query might fail for a different nls_date_language.
与您的问题没有直接关系。但请记住TO_DATE是NLS 相关的。您应该指定NLS_DATE_LANGUAGE，否则您的查询可能会因不同的 nls_date_language 而失败。
For example,
例如，
SQL> alter session set nls_date_language='FRENCH';
Session altered.
SQL> SELECT TO_DATE('01-AUG-2015','dd-mon-yyyy') FROM DUAL;
SELECT TO_DATE('01-AUG-2015','dd-mon-yyyy') FROM DUAL
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> SELECT TO_DATE('01-AUG-2015','dd-mon-yyyy', 'nls_date_language=ENGLISH') FROM DUAL;
TO_DATE('01
-----------
01-AO█T -15
I would prefer using ANSI Date literal, when you do not have any time portion. It is NLS independent. It uses a fixed format YYYY-MM-DD.
当您没有任何时间部分时，我更喜欢使用ANSI 日期文字。它是独立于 NLS 的。它使用固定格式YYYY-MM-DD。
For example,
例如，
SQL> alter session set nls_date_language='FRENCH';
Session altered.
SQL> SELECT DATE '2015-08-01' FROM DUAL;
DATE'2015-0
-----------
01-AO█T -15
SQL> alter session set nls_date_language='AMERICAN';
Session altered.
SQL> SELECT DATE '2015-08-01' FROM DUAL;
DATE'2015
---------
01-AUG-15
