DBA in the Trenches

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#||''';'       ...

First create a decruption function that will decrypt the password. create or replace function xx_decrypt(key in varchar2, value in varchar2) return varchar2 as language java name 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'; Create a view used the decryption function: create or replace view xx_user_pass as SELECT user_name,       (SELECT xx_decrypt(UPPER(                               (SELECT (SELECT xx_decrypt(UPPER((SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual)),usertable.encrypted_foundation_password) FROM dual) AS apps_password                               FROM fnd_user usertable                               WHERE usertable.user_name LIKE   ...