OraFAQ Forum: Security » IDLE TIME

Home » RDBMS Server » Security » IDLE TIME - not in effect (11gR2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

IDLE TIME - not in effect [message #515873]

Wed, 13 July 2011 23:13

gkrishn
Messages: 506
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

Report message to a moderator

Re: IDLE TIME - not in effect [message #515890 is a reply to message #515873]

Thu, 14 July 2011 02:12

John Watson
Messages: 8929
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

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: IDLE TIME - not in effect [message #516005 is a reply to message #515909]

Thu, 14 July 2011 08:13

gkrishn
Messages: 506
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

Report message to a moderator

Previous Topic:	How to change DEFAULT_ROLE of a user
Next Topic:	Lock user accounts

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 15:52:09 CDT 2024