[20180403]關於時區問題.txt

[20180403]關於時區問題.txt

--//昨天在寫文章時,再次遇到時區問題,連結blog.itpub.net/267265/viewspace-2152515/.
--//時不時在這些小問題犯錯誤,浪費時間,再次做一個記錄.

1.環境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> @ &r/pt2 'select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name=''ORA$AUTOTASK_CLEAN'' and  log_id=10956'
   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ ----------------------------------
         1          1 LOG_ID                         10956
                    2 LOG_DATE                       2018-04-02 16:25:41.666577 +08:00
                    3 OWNER                          SYS
                    4 JOB_NAME                       ORA$AUTOTASK_CLEAN
                    5 STATUS                         SUCCEEDED
                    6 ERROR_x0023_                   0
                    7 REQ_START_DATE                 2017-02-28 03:00:00.400000 -08:00
                    8 ACTUAL_START_DATE              2018-04-02 01:25:41.637226 -07:00
                    9 RUN_DURATION                   +000 00:00:00
                   10 INSTANCE_ID                    1
                   11 SESSION_ID                     28,5
                   12 SLAVE_PID                      938
                   13 CPU_USED                       +000 00:00:00.02
13 rows selected.

--//LOG_DATE,ACTUAL_START_DATE時間應該非常接近.自己當時的感覺有點犯糊塗了.

SYS@book> @ &r/pt2 'select LOG_DATE-ACTUAL_START_DATE diff from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name=''ORA$AUTOTASK_CLEAN'' and  log_id=10956'
   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ ---------------------------
         1          1 DIFF                           +000000000 00:00:00.029351

--//在仔細看才發現ACTUAL_START_DATE的時區-7,不是+8,也就是這個時間加上15小時才是北京時間.在這裡竟然浪費不小的時間.
--//實際上我以前就遭遇過一次這個問題,連結:blog.itpub.net/267265/viewspace-1726412/
--//當時的問題就是環境變數問題.如果定義如下不存在問題:
$ env |grep -i nls
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

--//再次做一次記錄.