- user logs on at time x
- User does nothing i.e. session is idle.
- 10 minutes later, you look at the last activity and its BEFORE X!
Ran the following script off metalink (Doc id = 16728.1):
select sid, to_char((sysdate - (hsecs - value)/(100*60*60*24)),
'dd-mon-yy hh:mi:ss') last, to_char(sysdate, 'dd-mon-yy hh:mi:ss') curr,
(hsecs - value)/(100) secs, (hsecs - value)/(100*60) mins
from v$timer, v$sesstat
where statistic# = (select statistic# from v$statname
where name = 'process last non-idle time');
(the ouput is edited to only show last non idle time). Note the 2005 entries
Ok, this is tru64 unix, running 'date'on the server brings the correct date and time, i.e. today's date and time in gmt.
What could cause this? The knock on effect is that we have a job that kills idle sessions after 60 mintes, but in reality these sessions are being wiped out much earlier than this.
Its oracle 9.2.0.3 (and 9.2.0.4, another database displays the same sysmptons) on Compaq Tru64.
I can't tell if that metalink script is a pile of crap, and whether when looking at lastactivity through OEM is using the same mechanism. We've only noticied this since the clock change at the weekend, although the clock on the server was not changed.
A colleague bounced it yesterday, and it seemed ok for a while, that is to say no sessions where timing out. This morning sessions' started to be sniped prematurely.
The time comes back correctly when you issue the 'date' command on the server. How else can I check the server clock?
Looks like when the server was bounced your server started to read the date in USA format (MM-DD-YYYY)... 11-Mar-2005 is really 03-Nov-2004, but with the date the wrong* way round Oracle resolves it as the next date it can be (i.e. 2005).
Yes, that metalink query is a bunch of crap. Actualy, if you look at its creation date, you'll see that it's from 1994!!!! Those were the days of Oracle7.0, werent they? So to be fair - that query is probably still valid for Oracle7 (and maybe for some later releases), but it certanly is not valid for 9i any more. Since Oracle7, some of the statistics meaning have drasticaly changed, obviously also including "'process last non-idle time".
Besides, when that query was written, that was the only way to obtain the information when a particular session was last active. But nowadays (at least since 8i, maybe even earlier) you have much easier way available to get that information. In V$SESSION there is a column LAST_CALL_ET which displays the time (in seconds) since a particular session isued the last request to the database.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?