6 Replies Latest reply: May 27, 2013 10:40 PM by Ora_83 RSS

    how to kill automatically blocking session

    Ora_83
      Hi

      I have a database username called "app" which comes from application server.

      If this user ("app") is blocked more than 5 minutes, I want the holder to be killed automatically.

      How can I achieve this ?
        • 1. Re: how to kill automatically blocking session
          sb92075
          Ora_83 wrote:
          Hi

          I have a database username called "app" which comes from application server.

          If this user ("app") is blocked more than 5 minutes, I want the holder to be killed automatically.

          How can I achieve this ?
          post the SQL & results that show the proper identity of the "holder".


          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: how to kill automatically blocking session
            Ora_83
            Hi

            Basically

            if (s1.seconds_in_wait >300) and If s2.username='APP'
            then
            alter system kill sesion 's1.sid,s1.serial#'

            SELECT s1.username,s1.sid,s1.serial#,s2.username,s2.sid
            FROM v$lock l1, v$session s1, v$lock l2,v$session s2,v$sql sqlt1, v$sql sqlt2
            WHERE s1.sid =l1.sid
            AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
            AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
            • 3. Re: how to kill automatically blocking session
              sb92075
              Ora_83 wrote:
              Hi

              I have a database username called "app" which comes from application server.

              If this user ("app") is blocked more than 5 minutes, I want the holder to be killed automatically.

              How can I achieve this ?
              which part of the SQL below filters for the 5 minute blockage above?
              SELECT s1.username, 
                     s1.sid, 
                     s1.serial#, 
                     s2.username, 
                     s2.sid 
              FROM   v$lock l1, 
                     v$session s1, 
                     v$lock l2, 
                     v$session s2, 
                     v$sql sqlt1, 
                     v$sql sqlt2 
              WHERE  s1.sid = l1.sid 
                     AND s2.sid = l2.sid 
                     AND sqlt1.sql_id = s2.sql_id 
                     AND sqlt2.sql_id = s1.prev_sql_id 
                     AND l1.BLOCK = 1 
                     AND l2.request > 0 
                     AND l1.id1 = l2.id1 
                     AND l2.id2 = l2.id2; 
              • 4. Re: how to kill automatically blocking session
                Ora_83
                Hi sb,

                My apologies.


                if (s2.seconds_in_wait >300) and If s2.username='APP'
                then
                alter system kill sesion 's1.sid,s1.serial#'
                • 5. Re: how to kill automatically blocking session
                  sb92075
                  Ora_83 wrote:
                  Hi sb,

                  My apologies.


                  if (s1.seconds_in_wait >300) and If s2.username='APP'
                  then
                  alter system kill sesion 's1.sid,s1.serial#'
                  ALTER SYSTEM statement can not be executed directly within PL/SQL procedure.
                  you must (ab)use EXECUTE IMMEDIATE

                  how often does this procedure get started; every second, every 5 seconds, every 10 seconds, or ????
                  • 6. Re: how to kill automatically blocking session
                    Ora_83
                    Hi sb,

                    it should run every minute. I can schedule the task once I can write the procedure