From mark@cool-tools.co.uk Tue, 06 Mar 2001 02:53:34 -0800 From: "Mark Leith" Date: Tue, 06 Mar 2001 02:53:34 -0800 Subject: RE: Old INACTIVE and KILLED sessions Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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@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@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@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@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@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@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@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).