I've been noticing lately that the database does not seem to be cleaning up after itself and the processes just keep climbing and climbing. Some sessions are more than two weeks old. I thought this was a bit weird and when our monitoring system started alerting to the number of sessions I realised there was a lot of dead session that are inactive but not being cleaned up. This is probably due to pooling and the way the DB is trying to save resources but in the EBS once a user closes a form they will not be using that session again. This sql give me the code to kill the session both in the DB as well as on the server itself: select vp.SPID,vs.username, UPPER(vs.program) program, logon_time, floor(vs.last_call_et / 60 ) min_inactive ,floor(vs.last_call_et / 60 /60 ) hr_inactive, vs.status, vs.status ,'alter system kill session '''||vs.sid||','||vs.serial#||''';' ...
Working my way through the dba day to day activities sharing what I learn along the way. Mostly a place to save my own notes for later use and hopefully someone else might find it useful.