We have a client program that leaves lots of open sessions. These sessions take up server resources and we wanted to find a way to locate and kill them.
Thanks to Natasa Oikonomidou from Softworks who provided the code, I ended up with the following solution:
Create a the following procedure on the sys schema.
create or replace PROCEDURE inactive_session_killer IS now DATE; BEGIN now := sysdate; FOR rec IN (SELECT sid, serial#, username, schemaname, osuser, terminal, machine, PROGRAM, status, type, saddr, logon_time, now - last_call_et / 86400 last_activity FROM v$session WHERE username = 'XXXXX' -- user name of open session "leaver" AND type = 'USER') LOOP IF rec.last_activity <( now - 8 / 24) THEN -- inactive for more than 8 hours -- you can keep a log table here: inserrt into ... EXECUTE IMMEDIATE 'alter system kill session ''' || rec.sid || ',' || rec.serial# || ''' immediate'; END IF; END LOOP; END;
Using SQLPlus or SQL Developer execute the following
VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT(:jobno, 'inactive_session_killer;', SYSDATE, 'SYSDATE + 1/24'); -- runs every hour commit; END;
... and that does it.