Home » RDBMS Server » Security » IDLE TIME - not in effect (11gR2)
IDLE TIME - not in effect [message #515873] Wed, 13 July 2011 23:13 Go to next message
gkrishn
Messages: 487
Registered: December 2005
Location: Putty a dark screen
Senior Member
# I am trying to list all users that are "Idle" for more than a day. (ie, Idle ,and on same wait).

select s.username,s.sid,round(w.seconds_in_wait/60/60) "Idle Hour"
from v$session_wait w,v$session s where w.wait_class='Idle' and w.wait_time='0' and w.seconds_in_wait>86400
and s.sid=w.sid and
s.username is NOT NULL order by 3,1 desc
/
USERNAME          SID  Idle Hour
---------- ---------- ----------
SCOTT         2147        176
SCOTT          724        350
SCOTT          861        350
SCOTT          586        350
SCOTT         1864        351
SCOTT         1998        400
SCOTT         1424        401


I have checked SCOTT user profile, has this but
IDLE_TIME                        KERNEL   240
CONNECT_TIME                     KERNEL   UNLIMITED


Any idea why IDLE_TIME is not working correctly ? or is it problem with my first query ? Thanks
Re: IDLE TIME - not in effect [message #515890 is a reply to message #515873] Thu, 14 July 2011 02:12 Go to previous messageGo to next message
John Watson
Messages: 4683
Registered: January 2010
Location: Global Village
Senior Member
Have you set your RESOURCE_LIMIT instance parameter to true?

[update - typo]

[Updated on: Thu, 14 July 2011 02:13]

Report message to a moderator

Re: IDLE TIME - not in effect [message #515909 is a reply to message #515890] Thu, 14 July 2011 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
New way to check idle sessions, till now I used the old one: LAST_CALL_ET (in seconds) for sessions with "status = 'INACTIVE'".

Comments on SQL:
"w.wait_time='0'" wait_time is a NUMBER, '0' is a string, compare things that are the same type, 0 for instance.
v$session_wait is embedded inside v$session these days, you have not to join both.

Regards
Michel



Re: IDLE TIME - not in effect [message #516005 is a reply to message #515909] Thu, 14 July 2011 08:13 Go to previous message
gkrishn
Messages: 487
Registered: December 2005
Location: Putty a dark screen
Senior Member
@Watson , good catch ! thanks

@Mike.Thanks.i will do some RD on what you said. Thanks
Previous Topic: How to change DEFAULT_ROLE of a user
Next Topic: Lock user accounts
Goto Forum:
  


Current Time: Wed Oct 22 19:10:56 CDT 2014

Total time taken to generate the page: 0.10405 seconds