Re: Autologout of idle users

From: Jared Still <jkstill_at_cybcon.com>
Date: Wed, 06 Jun 2001 20:45:18 -0700
Message-ID: <F001.0031F94D.20010606203021@fatcity.com>

On Wednesday 06 June 2001 16:56, Mark Liggayu wrote:
> What commant should I use to automatically logout uses in oracle who has
> been idle for more than 30 minutes?
> We have UNIX as an OS.
>
> Thanks,
> Mark

Mark,

You can create a profile that has an idle time limit.

The problem with that is that the session is not really removed from the instance, but the session status is changed to SNIPED.

This means that when the user tries to do more work after the session has timed out, an error will be generated.

e.g. ORA-02396: exceeded maximum idle time, please connect again

While this process makes the user login again, it does nothing to release resources. Try this experiment. Create profile, assign it to a user, login via sqlplus and wait for the session to timeout. At that time look at the session info and you will see that database/OS resources are still being consumed.

create profile idle_limit limit idle_time 1; alter user scott profile idle_limit;

You will notice that while the idle_limit is set to 1 minute, the timeout will not occur until 3 minutes, as that is the minimum allowed idle_time.

Also, if you don't have resource limiting turned on, do this:

alter system set resource_limit = true;

Check v$session status, and server process id:

select

   s.username,
   s.sid,
   s.serial#,
   p.pid ppid,
   s.status,
   s.machine,
   s.osuser,

   substr(s.program,1,20) client_program,    s.process client_process,
   substr(p.program,1,20) server_program,    to_char(p.spid) spid,
   to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time,

idle time
days added to hours --( trunc(LAST_CALL_ET/86400) * 24 ) || ':' ||
days separately substr('0'||trunc(LAST_CALL_ET/86400),-2,2) || ':' ||
hours substr('0'||trunc(mod(LAST_CALL_ET,86400)/3600),-2,2) || ':' ||
minutes substr('0'||trunc(mod(mod(LAST_CALL_ET,86400),3600)/60),-2,2) || ':' || --seconds substr('0'||mod(mod(mod(LAST_CALL_ET,86400),3600),60),-2,2) idle_time from v$session s, v$process p where s.username is not null
use outer join to show sniped sessions in
v$session that don't have an OS process and p.addr(+) = s.paddr
uncomment to see only your own session --and userenv('SESSIONID') = s.audsid order by username, sid;

You will likely see that SCOTT is still logged in with a STATUS of SNIPED;

Take a look at v$wait and you will see that SCOTT is being waited on:

select

   s.username username,
   e.event event,
   s.sid,
   e.p1text,
   e.p1,
   e.p2text,
   e.p2,
   e.wait_time,
   e.seconds_in_wait,
   e.state

from v$session s, v$session_wait e
where s.username is not null

and s.sid = e.sid
order by s.username, upper(e.event);

Something new happened in 8.1.7 while testing this, after a few more minutes the session for SCOTT was actually removed. I can't remember what version it was last time I tested this, but the session remained until SCOTT logged out or the session was killed.

One thing that hasn't changed is that the shadow process will continue to exist until SCOTT logs out or you kill the process.

If you use IDLE_LIMIT, you will need to check frequently for SNIPED sessions and kill the OS process to avoid collecting a lot of extra useless processes.

Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jun 06 2001 - 22:45:18 CDT