Sometime we have connections that remain logged “ad infinitum” in the database, either hung or deliberately maintained by the user that has not disconnect from the application, it is good to clean these connections that consume resources. I include below a small procedure to perform this work.
Please, be careful and probe the script and adapt it you needs to before use it in production enviroment. A good idea is the first time comment the kill command and see the output.
planning in the scheduler or crontab.
ALTER SESSION ENABLE COMMIT IN PROCEDURE; CREATE OR REPLACE PROCEDURE CLEAN_UP_CONNECTIONS IS STAMENT1 VARCHAR2(200); Sid_AUX VARCHAR2(30); Serial#_AUX VARCHAR2(30); CURSOR pri IS SELECT SID, SERIAL#, FROM V$SESSION WHERE TRUNC(LOGON_TIME) < TRUNC(SYSDATE) AND -- Yesterday connections USERNAME NOT IN ('ANR','UWE','HANNA') -- USERS dont kill never and USERNAME NOT in ('SYS','SYSTEM') -- When we connect like sysdba it Type = user, dont kill AND TYPE != 'BACKGROUND' -- Oracle processes, dont kill, bad idea -- list of connections to kill, filter by machine in this case AND ( MACHINE LIKE 'DOMAIN1\MKT%' OR MACHINE LIKE 'DOMAIN1\FINANCIAL%' OR MACHINE LIKE 'WINDOWSXP%' OR MACHINE LIKE '%RH4%' OR MACHINE LIKE '%DOMAIN5\CALLCENTER%' ); BEGIN FOR cursor_aux in pri LOOP Sid_aux := cursor_aux.Sid; Serial#_aux := cursor_aux.Serial#; STAMENT1 := 'ALTER SYSTEM DISCONNECT SESSION ''' || Sid_aux || ','|| Serial#_aux || '''IMMEDIATE'; Execute Immediate(STAMENT1); END LOOP; COMMIT; END; /
- DISCONNECT SESSION is used, that is meant to eliminate connections MTS .
- Could be interesting to include a log table, to keep the connections erasing. Example, would make an insert with the username.
HTH – Antonio NAVARRO