sql - 使用闪回查询时出现 BST 到 GMT Oracle 错误 (ORA-08186)

Question

希望有人能赐教...

今天早上我到达办公室，发现自动化(通过 OS CRON)SQL*Plus 脚本在 0100BST 开始失败，并且在从 BST 切换到 GMT 期间一直失败，直到 0230BST，并出现以下 Oracle 错误

"*ERROR at line 20:
ORA-08186: invalid timestamp specified"

下面的 SQL 已编辑了一些标识名称，但在语法上与失败的 SQL 相同。

 SELECT TO_CHAR( MIN(ioh.mdstamp), 'DD-MON-YY hh24:mi:ss')   First_Update
       , TO_CHAR( MAX(ioh.mdstamp), 'DD-MON-YY hh24:mi:ss')  Last_Update
       , ioh.consignment
       , ioh.order       
       , CASE ioh.ord_type
            WHEN 'T' THEN 'STORE MOVE'
            WHEN 'S' THEN 'CUST DEL'
         END                    Movement_Type
       , ioh.status             Order_Status         
       , ioh.site_id            Site
       , COUNT(\*)               Attempts
    FROM table1
         VERSIONS
          BETWEEN TIMESTAMP 
                  from_tz(cast((sysdate) as timestamp),'Europe/London') - INTERVAL '20' MINUTE
              AND MAXVALUE ioh
   WHERE versions_operation = 'I'
     And ioh.client_id      = 'client1'
     AND ioh.status         = 'Cancelled'
     AND ioh.MSTATUS        = 'Pending'     
GROUP BY ioh.consignment
       , ioh.order
       , ioh.ord_type
       , ioh.status         
       , ioh.site_id
ORDER BY ioh.consignment
       , ioh.order_id
       , 2

环境是:

• 操作系统:RHEL7.2
• 数据库:Oracle Database 12c 企业版版本 12.1.0.2.0 - 64 位生产
• SQL*Plus:版本 12.1.0.2.0

该脚本在通过操作系统 Cron 调用的 BASH 包装脚本中每 15 分钟执行一次。

sqlplus -s $CONNECT_DCS << ! >> ${f_SQLResults}
        @${SQL_SCRIPTS}/${__SCRIPTNAME_NOEXT}.sql;  
        exit;
!

另一个脚本捕获了操作系统的 BST 到 DST 的变化:

---------------------------------------
Checking for rejections due to invalid address
Sun 27 Oct **01:47:11 BST** 2019
got count 0
0 - no rejections
---------------------------------------
---------------------------------------
Checking for rejections due to invalid address
Sun 27 Oct **01:02:10 GMT** 2019
got count 0
0 - no rejections
---------------------------------------

故障在 0100h BST 开始发生，并持续发生，直到 0230h BST(如果在 0200BST 没有发生 BST 到 DST 的更改)。当我确保时区是“欧洲/伦敦”时，我认为数据库会自动处理此 DST 更改，但我无法理解为什么它没有。我确实想到的一个想法是，它可能与使用 MAX SCN 值或 NLS_TERRITORY bwing 设置为“AMERICA”有关，尽管我曾想过在 sql q 中显式设置时区可以解决此问题。

设置以下数据库配置:

SQL> select dbtimezone from dual;

DBTIME
------
+00:00

1 row selected.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
Europe/London

1 row selected.

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
28-OCT-19 09.30.36.653265 AM +00:00

1 row selected.

SQL> !date
Mon 28 Oct 09:30:42 GMT 2019

SQL> set lines 200
SQL> l
  1* select * from v$nls_parameters
SQL> /

PARAMETER                                                        VALUE                                                                CON_ID
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
NLS_LANGUAGE                                                     AMERICAN                                                                  0
NLS_TERRITORY                                                    AMERICA                                                                   0
NLS_CURRENCY                                                     $                                                                         0
NLS_ISO_CURRENCY                                                 AMERICA                                                                   0
NLS_NUMERIC_CHARACTERS                                           .,                                                                        0
NLS_CALENDAR                                                     GREGORIAN                                                                 0
NLS_DATE_FORMAT                                                  DD-MON-RR                                                                 0
NLS_DATE_LANGUAGE                                                AMERICAN                                                                  0
NLS_CHARACTERSET                                                 AL32UTF8                                                                  0
NLS_SORT                                                         BINARY                                                                    0
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM                                                            0

PARAMETER                                                        VALUE                                                                CON_ID
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM                                                  0
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR                                                        0
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR                                              0
NLS_DUAL_CURRENCY                                                $                                                                         0
NLS_NCHAR_CHARACTERSET                                           AL16UTF16                                                                 0
NLS_COMP                                                         BINARY                                                                    0
NLS_LENGTH_SEMANTICS                                             CHAR                                                                      0
NLS_NCHAR_CONV_EXCP                                              FALSE                                                                     0

19 rows selected.

SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'tzr') FROM dual;

TO_CHAR(SYSTIMESTAMP,'TZR')
--------------------------------
+00:00

1 row selected.

SQL> !date +"%Z %z"
GMT +0000

SQL> !timedatectl
      Local time: Mon 2019-10-28 11:57:36 GMT
  Universal time: Mon 2019-10-28 11:57:36 UTC
        RTC time: Mon 2019-10-28 11:57:36
       Time zone: Europe/London (GMT, +0000)
     NTP enabled: yes
NTP synchronized: yes
 RTC in local TZ: no
      DST active: no
 Last DST change: DST ended at
                  Sun 2019-10-27 01:59:59 BST
                  Sun 2019-10-27 01:00:00 GMT
 Next DST change: DST begins (the clock jumps one hour forward) at
                  Sun 2020-03-29 00:59:59 GMT
                  Sun 2020-03-29 02:00:00 BST

SQL>

如果有人可以提供帮助，我将非常感激。

干杯

PS:刚刚注意到我们的 glogin.sql 有以下行:“alter session set time_zone='Europe/London';”

Answer 1

您需要更改 NLS_TIME 和 NLS_TIMESTAMP 参数。可以通过发出以下命令来应用快速解决方法:

SQL> ALTER SESSION SET NLS_TIME_FORMAT         = 'HH24:MI:SSXFF';
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT    = 'DD/MM/RRRR HH24:MI:SSXFF';
SQL> ALTER SESSION SET NLS_TIME_TZ_FORMAT      = 'HH24:MI:SSXFF TZR';
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'DD/MM/RRRR HH24:MI:SSXFF TZR';

因为您正在寻找 HH24 格式的小时

例如查询中的TO_CHAR( MIN(ioh.mdstamp), 'DD-MON-YY hh24:mi:ss')。