Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> How to invoke Oracle clean up process to remove INACTIVE sessions?
Hi DBAs,
My system makes huge number of calls to remote databases through db links. The sessions made through the db links are open at the remote databases for a long time. Though, Oracle could clean up the INACTIVE sessions, it "considers" the sessions as INACTIVE only after approximately 4 - 5 hours which sounds like too much time for us.
By any chance can we control this "auto-cleanup" timing and bring it down to around 30 minutes or so. Every INACTIVE session after 30 minutes, should be killed? Please let me know your views on the same.
And, can the remote databases apply any upper limit on the connections and once it is reached, re-use the existing connections for further requests without using MTS? ( we tried pre spawned server processes (listener.ora) but it is still not enforcing the re-use of existing connections!)
1st snapshot
SELECT s.machine "Machine", count(*), min(s.logon_time) "Connect Time",
max(SYSDATE - (s.last_call_et / 86400)) "Last Call", min(SYSDATE - (s.last_call_et / 86400)) "Min_Last_Call"
FROM v$session s, v$process p, SYS.v_$sess_io si
WHERE s.paddr = p.addr(+) AND si.SID(+) = s.SID
and machine like '%e10k%'
group by s.machine
ORDER BY 1 DESC
Machine, COUNT(*), Connect Time, Last Call, Min(Last_Call) A , 94, 09/11/2004 22:12:11, 10/11/2004 11:17:21, 10/11/2004 06:38:12 B , 91, 09/11/2004 22:05:04, 10/11/2004 11:17:21, 10/11/2004 07:55:42 C , 85, 09/11/2004 22:05:49, 10/11/2004 11:17:21, 10/11/2004 09:46:19
After a while.... 2nd snapshot
Machine, COUNT(*), Connect Time, Last Call, Min(Last_Call) A , 91, 09/11/2004 22:12:11, 10/11/2004 11:26:26, 10/11/2004 06:42:35 B , 90, 09/11/2004 22:05:04, 10/11/2004 11:26:26, 10/11/2004 07:55:51 C , 77, 09/11/2004 22:05:49, 10/11/2004 11:26:26, 10/11/2004 09:46:28
Note - The Last column reveals that the INACTIVE sessions in the first snapshot are disappearing VERY slowly.
Environment
RDBMS : 8.1.7 OS : Solaris at source and different OS at remote databases.
Thanks,
Arul.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 10 2004 - 06:05:59 CST
![]() |
![]() |