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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
