RE: Autologout of idle users
Mark, Create profile and assign the profile to the user. Also, enable resource limits to take effect. Following is an example for creating a profile. Step 1. CREATE PROFILE duh1 LIMIT SESSIONS_PER_USERUNLIMITED CPU_PER_SESSIONUNLIMITED IDLE_TIME 30 ; Step 2. Assign the profile to the user or to the users whom you want to limit idle sessions time. ALTER USER SCOTT PROFILE duh1; Step 3. Connect to the system through SQL with DBA privileges. ALETER SYSTEM RESOURCE_LIMIT TRUE; In the above example, if the user exceeds idle time 30 minutes, then Oracle end's the session. ONE CAUTION: Be very careful with some of the irate developers or with lazy developers who do not log out ever. To avoid the clashes, send a mail to every body that you are implementing this idle time and let evey body know in advance. Rao -Original Message- Sent: Thursday, June 07, 2001 4:31 AM To: Multiple recipients of list ORACLE-L Check out PROFILES which can be used to achieve what you are after. hth connor --- Mark Liggayu [EMAIL PROTECTED] 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rao, Maheswara 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).
RE: Autologout of idle users
But its so much more fun not to tell them in my experience. Case in point. After constant reminders (all mails copied to manager) to, amongst other things, remember to log out, an auto log out procedure is adopted. First irate developer/user rings up and complains in a less than friendy manner. I suggest that they take it up with my manager. My manager dusts them off saying they were warned. All is right with the world and I have had a little bit of sport as well. Heh heh Lee ONE CAUTION: Be very careful with some of the irate developers or with lazy developers who do not log out ever. To avoid the clashes, send a mail to every body that you are implementing this idle time and let evey body know in advance. Rao -Original Message- Sent: Thursday, June 07, 2001 4:31 AM To: Multiple recipients of list ORACLE-L Check out PROFILES which can be used to achieve what you are after. hth connor --- Mark Liggayu [EMAIL PROTECTED] 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rao, Maheswara 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). The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe 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).
Autologout of idle users
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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu 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).
Re: Autologout of idle users
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).
Re: Autologout of idle users
This can be done using Profiles, check the Oracle Admin Guide chapter on Managing Users Resources. http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76956/toc.htm 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos 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).
