- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Oracle SYSDATE show old date
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-24-2006 07:39 AM
тАО01-24-2006 07:39 AM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-24-2006 07:52 AM
тАО01-24-2006 07:52 AM
Re: Oracle SYSDATE show old date
is the local time correct?
select logon_time from v$session where sid=1;
is this time ok?
Regards
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-24-2006 07:54 AM
тАО01-24-2006 07:54 AM
Re: Oracle SYSDATE show old date
SQL> select logon_time from v$session where sid=1;
LOGON_TIM
---------
09-JAN-06
SQL>
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-24-2006 07:56 AM
тАО01-24-2006 07:56 AM
Re: Oracle SYSDATE show old date
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-24-2006 08:10 AM
тАО01-24-2006 08:10 AM
Re: Oracle SYSDATE show old date
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-24-2006 09:10 AM
тАО01-24-2006 09:10 AM
Re: Oracle SYSDATE show old date
Tue Jan 24 14:08:41 PST 2006
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-24-2006 09:10 AM
тАО01-24-2006 09:10 AM
Re: Oracle SYSDATE show old date
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-24-2006 12:56 PM
тАО01-24-2006 12:56 PM
SolutionThis 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-24-2006 01:28 PM
тАО01-24-2006 01:28 PM
Re: Oracle SYSDATE show old date
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-24-2006 11:40 PM
тАО01-24-2006 11:40 PM
Re: Oracle SYSDATE show old date
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