Oracle Client showing time an hour behind

  • From: Saad Khan <saad4u@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 10 May 2010 17:43:42 -0400

Hi fellows,

We have a oracle environment with 64-bit oracle 10.2.0.4 running on AIX 5.3.

I was notified about one strange behaviour by the app support guyz after the
last DST change.

When we check the timestamp on oracle server, it shows the correct timestamp
on both OS as well as Oracle. However, when run the command from sqlplus
client or any other oracle client tool or the app, it shows the
timestamp/sysdate of one hour behind the actual time.

The database server timing is in EDT. The app doesnt use any TSTZ or TSLTZ
datatypes but I know there are data dictionary columns with that.

I ran the scripts from one of the metalink notes and found the following
details:
SQL> select * from TZ$overview;

USAGE_TYPE VALUE
--------------------------- ----------------------------------------
DESCRIPTION
------------------------------------------------------------------------
DBTIMEZONE -05:00
Database Time Zone, if this is an 'offset' then not affected by DST

NAMED_TSTZ_TABLE_USE_NONSYS 0
Number of TSTZ columns using named time zones in tables not owned by SYS

NAMED_TSTZ_TABLE_USE_SYS 12
Number of TSTZ columns using named time zones in tables owned by SYS


USAGE_TYPE VALUE
--------------------------- ----------------------------------------
DESCRIPTION
------------------------------------------------------------------------
NAMED_TSTZ_VIEW_USE_NONSYS 0
Number of TSTZ columns using named time zones in views not owned by SYS

NAMED_TSTZ_VIEW_USE_SYS 23
Number of TSTZ columns using named time zones in views owned by SYS

TSLTZ_TABLE_USE 0
Number of TSLTZ columns used in tables


USAGE_TYPE VALUE
--------------------------- ----------------------------------------
DESCRIPTION
------------------------------------------------------------------------
TSLTZ_VIEW_USE 0
Number of TSLTZ columns used in views

TZ_ARGUMENTS 0
Number of PL/SQL objects with Time Zone arguments

---

I found on one of the metalink note that rel 10.2.0.4 has the patchset for
DST change so it doesnt require any patch, but we are still encountering
this issue. I also tried after upgrading the oracle client to 10.2.0.4 but
same result. Any suggestion??

Thanks for the help.

Saad

Other related posts: