Connection Cleanup

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;
/

 

 

NOTES;

  • 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s