5 Replies Latest reply on May 28, 2013 8:16 AM by Billy~Verreynne

    procedure help

    Ora_83
      Hi
      How can I achieve this ?

      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;
        • 1. Re: procedure help
          Achyut K
          Ora_83 wrote:
          Hi
          How can I achieve this ?

          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;
          Hi,

          Prepare the alter statement by passing the SID and SERIAL and use EXECUTE IMMEDIATE statment to execute

          Regards,
          Achyut K
          • 2. Re: procedure help
            KPR
            Hi

            Use CASE statement and execute immediate statements in your select statement.

            KPR
            • 3. Re: procedure help
              Achyut K
              Ora_83 wrote:
              Hi
              How can I achieve this ?

              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;
              Hi,
              SQL> set serveroutput on
              SQL> DECLARE
                2  l_str varchar2(40):='ALTER SYSTEM KILL SESSION ';
                3  l_delim varchar2(1):='''';
                4  l_hash varchar2(1):='#';
                5  l_sid number:=740; ---From query
                6  l_serial number:=515151;--from query
                7  l_final_str varchar2(200):=l_str||l_delim||l_sid||','||l_serial||l_hash||l_delim;
                8  BEGIN
                9  dbms_output.put_line(l_final_str);
               10  --EXECUTE IMMEDIATE l_final_str; ---to be executed 
               11  END;
               12  
               13  .
              SQL> /
              ALTER SYSTEM KILL SESSION '740,515151#'
              
              PL/SQL procedure successfully completed.
              
              SQL> 
              Regards,
              Achyut K
              • 4. Re: procedure help
                Ora_83
                Hi Achyut,

                How can I combine my sql with your plsql ? or how can I convert it to procedure ?
                • 5. Re: procedure help
                  Billy~Verreynne
                  Ora_83 wrote:

                  How can I achieve this ?
                  How about doing it correctly?

                  Killing sessions means you/developer/user looses. You've given a server an instruction to perform. Server session is killed. Instruction fails. You do not get results back. You lose.

                  The correct approach is
                  a) determine WHAT is waiting/hanging/spinning
                  b) determine WHY
                  c) FIX the root cause

                  The correct approach is not simply killing sessions because they are waiting...