Daylight Saving Time in Russia has been changed last year. Oracle published a FAQ on the support site about this: Russia abandons DST in 2011 – Impact on Oracle RDBMS [ID 1335999.1].
In short if you are using DATEs and TIMESTAMPs without time zone in your application, you are almost “safe” and there’s most likely no need to do anything in particular on the database software level. In our application we use DATE and TIMESTAMP without time zone, so I thought it all should be fine. Not until today when I found out that I’ve a difference of 1 hour between stored TIMESTAMP and current date. Turns out it’s client’s issue (not a rare situation). The client is a WebLogic running Oracle JDK version 6u26 (1.6.0_26). And, unlike Oracle RDBMS, Oracle JRE is not consistent with OS time, so when you create a new java.sql.Timestamp from a current time, it will take DST into account and will return value correspondingly – this was news to me. In my case without time zone patch it means 1 hour behind actual time. The time zone data is fixed in the JDK update 31 (release date 14 Feb 2012); it also can be patched easily with the help of Timezone Updater Tool:
$ ./bin/java -jar tzupdater.jar -V tzupdater version 1.3.45-b01 JRE time zone data version: tzdata2011g Embedded time zone data version: tzdata2011n $ ./bin/java -jar tzupdater.jar -u $ ./bin/java -jar tzupdater.jar -V tzupdater version 1.3.45-b01 JRE time zone data version: tzdata2011n Embedded time zone data version: tzdata2011n
Oracle’s internal JVM (aka Aurora) in Oracle 11.2.0.3 looks to be unaffected and works correctly. It reports itself as version 1.5.0_10:
SQL> create or replace and compile java source named "test" as
2 import java.util.Date;
3 public class test {
4 public static String getCurrentDate() {
5 return "" + new Date();
6 }
7 };
8 /
Java created.
SQL> sho err
No errors.
SQL>
SQL> create or replace function fnc_get_date return varchar2 as
2 language java name 'test.getCurrentDate() return java.lang.String';
3 /
Function created.
SQL> sho err
No errors.
SQL> select fnc_get_date, sysdate from dual;
FNC_GET_DATE SYSDATE
---------------------------------------- -----------------
Fri Feb 24 17:59:25 GMT+04:00 2012 20120224 17:59:25
And one more thing on the subject. I’d be happy if somebody would explain an anomaly with V$ACTIVE_SESSION_HISTORY.SAMPLE_TIME:
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> select startup_time from v$instance; STARTUP_TIME ----------------- 20111020 16:38:21 SQL> sho parameter nls NAME_COL_PLUS_SHOW_PARAM TYPE VALUE ---------------------------------------- ----------- ---------------------------- nls_calendar string GREGORIAN nls_comp string BINARY nls_currency string $ nls_date_format string YYYYMMDD HH24:MI:SS nls_date_language string AMERICAN nls_dual_currency string $ nls_iso_currency string AMERICA nls_language string AMERICAN nls_length_semantics string BYTE nls_nchar_conv_excp string FALSE nls_numeric_characters string ., nls_sort string BINARY nls_territory string AMERICA nls_time_format string HH.MI.SSXFF AM nls_time_tz_format string HH.MI.SSXFF AM TZR nls_timestamp_format string DD-MON-RR HH.MI.SSXFF AM nls_timestamp_tz_format string DD-MON-RR HH.MI.SSXFF AM TZR -- this is the question: v$active_session_history.sample_time lags 1 hour behind SQL> select systimestamp, sysdate, max(sample_time) from v$active_session_history; SYSTIMESTAMP SYSDATE MAX(SAMPLE_TIME) --------------------------------------------------------------------------- ----------------- ------------------------- 24-FEB-12 06.27.25.685962 PM +04:00 20120224 18:27:25 24-FEB-12 05.27.22.510 PM
