Oracle SYSDATE show old date

[Stephen Badgett]

Why does current date in Oracle show an old date?

SQL> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS')
as "SYSDATE"
from dual;
2 3
SYSDATE
------------------------
Wed 01-Jan-2003 10:00:00


Steve

[Oviwan]

Hi

is the local time correct?

select logon_time from v$session where sid=1;
is this time ok?

Regards

[Stephen Badgett]

Look right

SQL> select logon_time from v$session where sid=1;

LOGON_TIM
---------
09-JAN-06

SQL>

[A. Clay Stephenson]

What does the UNIX date command display when logged in as the same user?

[A. Clay Stephenson]

Oh, and just so we know we are on the same page, I assume that this is a local Oracle connection so that the UNIX date command and the Oracle sysdata function have something in common. Sysdate should do nothing more than invoke the time() system call and then pass that to localtime().

[Stephen Badgett]

SQL> host date
Tue Jan 24 14:08:41 PST 2006

[Stephen Badgett]

Yes this is a local server

[Indira Aramandla · Accepted solution]

Hi Steve,

This is what metalink says
The SYSDATE function simply performs a system-call to the Operating System to get the time (a "gettimeofday" call).

The OS (Unix) TZ environment variable influences the time that the OS will pass on to Oracle. So even though sysdate itself does not use the timezones in the database, it is influenced by the (non-Oracle) TZ environment variable on the OS.

To debug situations in which you have a unexplained difference between the oracle sysdate and the system time you see on Unix, use the following method:

telnet to the unix machine

connect using sqlplus in the telnet session:

1)) once through the listener using a tnsnames alias
$sqlplus user/password@[tnsnames alias]
SQL>select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

2) once trough a "local" ORACLE_SID connection
$env | egrep 'ORACLE_SID'
$sqlplus user/password
SQL>select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

Check that the time in the banner of sqlplus ( SQL*Plus: Release 10.1.0.4.0 - Production on Wo Jan 11 15:05:46 2006 ) is reflecting the time based on the current TZ set in the Unix (!) session.

If the results are different this means that the listener is started with a different TZ then you current user environment. To resolve this simply stop and start listener with the TZ you want to use. if you are using MTS then you might see a correct result with a dedicated connection, in that case stop and start also the database with the correct TZ.


Indira A

[Hein van den Heuvel]

Is that clock ticking, or stuck at 1-Jan-2003?

What exact Oracle version/platform?

A broken timezone setting seems like the most likely cause.

You may also want do an strace on your Oracle slave and actually 'see' the gettimeofday.

fwiw,
Hein.

[Yogeeraj_1]

hi steve,

Just to clarify a bit, can you please post the output like the following:

$ export NLS_DATE_FORMAT="dd-mon-yyyy hh24:mi:ss"
$ sqlplus

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jan 25 16:39:53 2006

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Enter user-name: scott/tiger@mydb

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select sysdate from dual;

SYSDATE
--------------------
25-jan-2006 16:35:14

SQL>


thanks

kind regards
yogeeraj