| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Old INACTIVE and KILLED sessions
Yes. Sniped sessions are not being cleaned out properly by pmon. We kill the os process associated with the connection using the following script and pmon cleans up after that .
$ORACLE_HOME/bin/sqlplus -s / <<EOF > $TMPFILE
set pages 0
set feedback off
select p.spid from v\$process p,v\$session s
where s.paddr=p.addr
and s.status='SNIPED';
exit
EOF
for X in `cat $TMPFILE`
do
kill -9 $X
done
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com
"Trassens,
Christian" To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
<CTrassens_at_un cc:
i2.es> Subject: RE: Old INACTIVE and KILLED
sessions
Sent by:
root_at_fatcity.
com
03/06/01
05:35 AM
Please
respond to
ORACLE-L
Beware that SNIPED sessions could last forever when you limit the IDLE_TIME whether the users couldn't connect through the same connection. F.e. when you use sqlplus. Although the other profile limits work fine.
Regards.
> -----Mensaje original-----
> De: Tim Onions [SMTP:tim.onions_at_speechmachines.com]
> Enviado el: martes 6 de marzo de 2001 12:00
> Para: Multiple recipients of list ORACLE-L
> Asunto: RE: Old INACTIVE and KILLED sessions
>
> Indeed I'd forgotten about them! However, in my experience this will only
> mark the session as being SNIPED - the disconnect will only happen when
> the
> user tries to do somthing after the SNIP has occurred. This can be a pain
> if
> the session is idle due to network failure, application GPF etc. However,
> if
> the problem is down to users simply leaving the application running then
> for
> ages it can work.
>
> I guess it's all down to the nature of the particular problem!
>
> -----Original Message-----
> Sent: 06 March 2001 09:46
> To: Multiple recipients of list ORACLE-L
>
>
> You can also use profiles to do this. With profiles you can not only
limit
> the amount of time connected, but also the ammount of resources the user
> can
> take.
>
> Before you start playing with these, make sure that you set the init
> parameter:
> RESOURCE_LIMIT = TRUE
>
> Then to create a profile:
>
> create profile {profile_name} limit
> {resource_name) {value}..
> ..
> ..
>
> Examples of resource names are:
>
> CONNECT_TIME - value in minutes
> IDLE_TIME - Again in minutes
> PRIVATE_SGA - Kb or Mb
> CPU_PER_SESSION - limits the CPU time for a session I think in hundreths
> of
> seconds.
>
> so fo your needs you need to do something like
>
> create profile connect_timeout limit
> CONNECT_TIME 1440
> IDLE_TIME 720
>
> This will disconnect active users lasting longer than 24 hours, and all
> idle
> users after they have been idle for 12 hours.
>
> HTH
>
> Mark
>
>
> -----Original Message-----
> Ghosalkar
> Sent: Tuesday, March 06, 2001 02:05
> To: Multiple recipients of list ORACLE-L
>
>
> Henry,
>
> set the following parameter in the sqlnet.ora on the server and not on
the
> client.
> the unit is minutes. 10 mins shld be enuf. test with ur environment. if
> set
> less then maybe it may hv an impact on SQL*Net performance.
>
>
> SQLNET.EXPIRE_TIME= 10
>
>
> -Mandar
>
> > -----Original Message-----
> > From: "HENRY, Benoît" [mailto:HenryB_at_arjowiggins.be]
> > Sent: Monday, March 05, 2001 12:36 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Old INACTIVE and KILLED sessions
> >
> >
> >
> > Hi there,
> >
> >
> > I'm new to Oracle, coming from (gulp!) Informix.
> > I hope to find on this newsgroup a great help from you.
> > (it was the case in the Informix newsgroup ;-)))
> >
> > We are in the developing phase, and during tests,
> > our front-end application crashes often and our server
> > application who access the database still alive.
> >
> > They are remaining INACTIVE and KILLED processes in Oracle.
> > These eat a lot of memory (Oracle 8.0.5 on Windows NT4).
> >
> > I would like to find a init parameter (like transaction
> > time-out=24*60),
> > or a system procedure to disconnect automaticaly the user sessions
> > older than 24 hours, and free all the ressources.
> >
> > Can someone help me, or give some ideas or tips to manage this ?
> >
> > Thanks in advance
> >
> > Ben.
> >
> >
> >
> > select substr(sid,1,5) sid,status,process,type,
> > substr(logon_time,1,12) logon_time
> > from dba_v$session
> > order by status,logon_time;
> >
> >
> > SID STATUS PROCESS TYPE LOGON_TIM
> > ===== ======== ========= ========== =========
> > 1 ACTIVE 00123 BACKGROUND 02-MAR-01
> > 2 ACTIVE 00124 BACKGROUND 02-MAR-01
> > 3 ACTIVE 00125 BACKGROUND 02-MAR-01
> > 4 ACTIVE 00126 BACKGROUND 02-MAR-01
> > 5 ACTIVE 00127 BACKGROUND 02-MAR-01
> > 6 ACTIVE 00128 BACKGROUND 02-MAR-01
> > 37 ACTIVE 358:396 USER 05-MAR-01
> > 10 INACTIVE 160:250 USER 02-MAR-01
> > 22 INACTIVE 132:361 USER 02-MAR-01
> > 24 INACTIVE 353:352 USER 02-MAR-01
> > 23 INACTIVE 351:349 USER 02-MAR-01
> > 34 INACTIVE 409:402 USER 02-MAR-01
> > 33 INACTIVE 409:402 USER 02-MAR-01
> > 32 INACTIVE 386:385 USER 02-MAR-01
> > 31 INACTIVE 305:309 USER 02-MAR-01
> > 30 INACTIVE 386:385 USER 02-MAR-01
> > 29 INACTIVE 372:344 USER 02-MAR-01
> > 27 INACTIVE 369:368 USER 02-MAR-01
> > 26 INACTIVE 353:352 USER 02-MAR-01
> > 25 INACTIVE 369:368 USER 02-MAR-01
> > 21 INACTIVE 364:348 USER 02-MAR-01
> > 17 INACTIVE 337:336 USER 02-MAR-01
> > 18 INACTIVE 372:344 USER 02-MAR-01
> > 20 INACTIVE 132:361 USER 02-MAR-01
> > 19 INACTIVE 305:309 USER 02-MAR-01
> > 16 INACTIVE 322:329 USER 02-MAR-01
> > 35 INACTIVE 358:396 USER 05-MAR-01
> > 7 KILLED 351:349 USER 02-MAR-01
> > 15 KILLED 303:319 USER 02-MAR-01
> > 8 KILLED 321:318 USER 02-MAR-01
> > 12 KILLED 322:329 USER 02-MAR-01
> > 13 KILLED 337:336 USER 02-MAR-01
> > 14 KILLED 321:318 USER 02-MAR-01
> > 11 KILLED 303:319 USER 02-MAR-01
> > 9 KILLED 364:348 USER 02-MAR-01
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: =?iso-8859-1?Q?=22HENRY=2C_Beno=EEt=22?=
> > INET: HenryB_at_arjowiggins.be
> >
> > 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).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mandar Ghosalkar
> INET: MandarG_at_gsr-inc.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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mark Leith
> INET: mark_at_cool-tools.co.uk
>
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Tim Onions
> INET: tim.onions_at_speechmachines.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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Trassens, Christian INET: CTrassens_at_uni2.es 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Riyaj_Shamsudeen_at_i2.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 Tue Mar 06 2001 - 16:31:09 CST
 |
 |