如何在expdp中使用QUERY来仅提取最近3个月的数据 [英] How to use QUERY in expdp to extract only the last 3 months data

问题描述

在我的另一个话题中，我问如何仅提取最近3个月.但是，我设法仅从一个表中提取了过去三个月的数据，并从其他表中提取了所有数据.我的架构中有几个表，时间戳的列名不同.

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.

在我的par文件中，我有以下查询，但它不起作用.我收到了ORA-00911错误消息.我想知道以下查询的语法是否正确/可能.

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 .

    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');" 

推荐答案

QUERY = TABLE1，TABLE2:其中... TO_DATE('2015年8月1日'，'dd-mon-yyyy');"

QUERY=TABLE1,TABLE2:"where ... TO_DATE('01-AUG-2015','dd-mon-yyyy');"

在 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')" 

旁注:

与您的问题没有直接关系.但是请记住， TO_DATE 是 NLS依赖.您应该指定 NLS_DATE_LANGUAGE ，否则对于其他nls_date_language，查询可能会失败.

Not directly related to your issue. But remember TO_DATE is NLS dependent. You should specify the NLS_DATE_LANGUAGE, else your query might fail for a different nls_date_language.

例如，

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

当您没有任何时间部分时，我希望使用 ANSI日期文字.它是与NLS无关.它使用固定格式 YYYY-MM-DD .

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.

例如，

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

这篇关于如何在expdp中使用QUERY来仅提取最近3个月的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！