[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
--//再次做一次记录.
