操作系统的时区设置会影响数据库查询SYSDATE和SYSTIMESTAMP的值

This means that SYSTIMESTAMP, just like SYSDATE depends on Unix platforms on the UNIX time configuration (= Unix TZ variable) for the Unix session when the database and listener where started.

The SYSDATE and SYSTIMESTAMP function simply performs a system-call to the Operating System to get the time (a "gettimeofday" call).

下面通过简单的实验来证明：

SQL> select to_char(sysdate,‘DD-MON-YY HH24:MI:SS‘) from dual;

TO_CHAR(SYSDATE,‘DD-MON-YYHH24:MI:SS‘)
------------------------------------------------------
17-OCT-14 11:51:25             <<<<<这里输出日期为17号

SQL> connect sys/oracle@ora10g as sysdba
Connected.
SQL> select to_char(sysdate,‘DD-MON-YY HH24:MI:SS‘) from dual;

TO_CHAR(SYSDATE,‘DD-MON-YYHH24:MI:SS‘)
------------------------------------------------------
17-OCT-14 11:51:33

SQL> !
[oracle@tivoli02 ~]$ date
Fri Oct 17 11:51:38 CST 2014    <<<<<这里输出日期为17号

以上输出正常的时间，接下来改动时区环境变量之后做对照

export TZ=America/Anchorage

SQL> select to_char(sysdate,‘DD-MON-YY HH24:MI:SS‘) from dual;

TO_CHAR(SYSDATE,‘DD-MON-YYHH24:MI:SS‘)
------------------------------------------------------
16-OCT-14 19:53:50                        <<<<<这里输出日期为16号

SQL> connect sys/oracle@ora10g as sysdba
Connected.
SQL> select to_char(sysdate,‘DD-MON-YY HH24:MI:SS‘) from dual;

TO_CHAR(SYSDATE,‘DD-MON-YYHH24:MI:SS‘)
------------------------------------------------------
16-OCT-14 19:53:58

SQL> !
[oracle@tivoli02 ~]$ date
Thu Oct 16 19:54:06 AKDT 2014      <<<<<这里输出日期为16号

查看数据库alert日志：

Fri Oct 17 11:51:57 CST 2014       <<<停库日期为17号
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thu Oct 16 19:53:32 AKDT 2014       <<<启库日期为16号
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3

结合以上实验做阐述，请一定正常设置操作系统环境变量，避免不必要的麻烦。

-------------------------------------------------------------------------------------------------

本文来自于我的技术博客 http://blog.csdn.net/robo23

转载请标注源文链接,否则追究法律责任!

操作系统的时区设置会影响数据库查询SYSDATE和SYSTIMESTAMP的值

标签：database   ons   结合   and   cte   tar   processes   unix   一段