7 Replies Latest reply: Jun 12, 2013 2:27 AM by 1003546 RSS

    exact fetch returns more than requested number of rows

    Ora_83

      Hello,

       

      I have below procedure to kill blocking sesssion. It works fine if one row is returned from the select.
      How can I modify it to make it work for more than one rows ?

       

      CREATE OR REPLACE PROCEDURE kill_blocking IS
      l_sid        NUMBER (10);
      l_serial    NUMBER (10);
      l_wait      NUMBER (10);
      l_username  VARCHAR2 (20);
      sqlstmt      VARCHAR2 (1000);
      BEGIN
      SELECT s1.SID, s1.serial#, s2.seconds_in_wait, s2.username
      INTO l_sid, l_serial, l_wait, l_username
      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;
      IF l_username = 'APP' AND l_wait > 100
      THEN
      sqlstmt :=
      'ALTER SYSTEM KILL SESSION '''
      || TO_CHAR (l_sid)
      || ','
      || TO_CHAR (l_serial)
      || '''';
      DBMS_OUTPUT.put_line (sqlstmt);
      EXECUTE IMMEDIATE sqlstmt;
      END IF;
      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
      DBMS_OUTPUT.put_line ('No Data Found');
      END;
      /


      SQL> exec kill_blocking;
      BEGIN kill_blocking; END;

      *
      ERROR at line 1:
      ORA-01422: exact fetch returns more than requested number of rows
      ORA-06512: at "TK.KILL_BLOCKING", line 8
      ORA-06512: at line 1

        • 1. Re: exact fetch returns more than requested number of rows
          Ashu_Neo

          Somehow your implicit cursor in program is returning more than one record. Run the single query separately in sql editor. And check for number of records being fetched. Even in exception block, TOO_MANY_ROWS exception has not been handled! Thanks!

          • 2. Re: exact fetch returns more than requested number of rows
            Mahir M. Quluzade

            Hi,

             

            I little  change your script, please try

             

            CREATE OR REPLACE PROCEDURE kill_blocking IS
            l_sid        NUMBER (10);
            l_serial    NUMBER (10);
            l_wait      NUMBER (10);
            l_username  VARCHAR2 (20);
            sqlstmt      VARCHAR2 (1000);
            BEGIN
            FOR T IN (
            SELECT S1.SID l_sid, S1.SERIAL# l_serial, S2.SECONDS_IN_WAIT l_wait, S2.USERNAME l_username
            
            
            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)
            
            
            LOOP
            IF t.l_username = 'APP' AND t.l_wait > 100
            THEN
            sqlstmt :=
            'ALTER SYSTEM KILL SESSION '''
            || TO_CHAR (t.l_sid)
            || ','
            || TO_CHAR (t.l_serial)
            || '''';
            DBMS_OUTPUT.put_line (sqlstmt);
            EXECUTE IMMEDIATE SQLSTMT;
            END IF;
            END LOOP;
            
            
            EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
            DBMS_OUTPUT.put_line ('No Data Found');
            END;
            /
            
            

             

             

            Regards

            Mahir M. Quluzade

            • 3. Re: exact fetch returns more than requested number of rows
              BCV

              Hi,

              How can I modify it to make it work for more than one rows ?

               

              Use Your Code in Loop,Like Below,

               

               

               

              CREATE OR REPLACE PROCEDURE kill_blocking
              IS
                 l_sid        NUMBER (10);
                 l_serial     NUMBER (10);
                 l_wait       NUMBER (10);
                 l_username   VARCHAR2 (20);
                 sqlstmt      VARCHAR2 (1000);
              BEGIN
                 FOR i IN (SELECT s1.SID, s1.serial#, s2.seconds_in_wait, s2.username
              --INTO l_sid, l_serial, l_wait, l_username
                           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)
                 LOOP
                    IF i.username = 'APP' AND i.seconds_in_wait > 100
                    THEN
                       sqlstmt :=
                             'ALTER SYSTEM KILL SESSION '''
                          || TO_CHAR (i.sid)
                          || ','
                          || TO_CHAR (i.serial)
                          || '''';
                       DBMS_OUTPUT.put_line (sqlstmt);

                       EXECUTE IMMEDIATE sqlstmt;
                    END IF;
                 END LOOP;
              EXCEPTION
                 WHEN NO_DATA_FOUND
                 THEN
                    DBMS_OUTPUT.put_line ('No Data Found');
              END;

              • 4. Re: exact fetch returns more than requested number of rows
                1007945

                Hi

                 

                You can user the below procedure

                 

                 

                create or replace

                PROCEDURE kill_blocking_mig

                IS

                  l_sid      NUMBER (10);

                  l_serial  NUMBER (10);

                  l_wait    NUMBER (10);

                  l_username VARCHAR2 (20);

                  sqlstmt    VARCHAR2 (1000);

                 

                  CURSOR status_cus IS

                  SELECT s1.SID,s1.serial#,s2.seconds_in_wait,s2.username

                  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;

                BEGIN

                OPEN status_cus;

                loop

                fetch status_cus INTO l_sid, l_serial, l_wait, l_username;

                exit when status_cus%notfound;

                 

                  IF l_username    = 'APP' AND l_wait > 100 THEN

                    sqlstmt      := 'ALTER SYSTEM KILL SESSION ''' || TO_CHAR (l_sid) || ',' || TO_CHAR (l_serial) || '''';

                    DBMS_OUTPUT.put_line (sqlstmt);

                    EXECUTE IMMEDIATE sqlstmt;

                  END IF;

                  end loop;

                EXCEPTION

                WHEN NO_DATA_FOUND THEN

                  DBMS_OUTPUT.put_line ('No Data Found');

                END;

                • 7. Re: exact fetch returns more than requested number of rows
                  1003546

                  Try this.....this might work

                   

                  CREATE OR REPLACE PROCEDURE kill_blocking IS

                  type kb is Record(l_sid        NUMBER (10),

                          l_serial    NUMBER (10),

                          l_wait      NUMBER (10),

                          l_username  VARCHAR2 (20));

                  kb1 kb;

                  sqlstmt      VARCHAR2 (1000);

                  BEGIN

                  SELECT s1.SID, s1.serial#, s2.seconds_in_wait, s2.username

                  INTO kb1_info

                  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;

                   

                  loop

                  exit when kb1.l_username is null

                  IF kb1.l_username = 'APP' AND kb1.l_wait > 100

                  THEN

                      i:=i+1

                      sqlstmt :=

                      'ALTER SYSTEM KILL SESSION '''

                      || TO_CHAR (kb1.l_sid)

                      || ','

                      || TO_CHAR (kb1.l_serial)

                      || '''';

                      DBMS_OUTPUT.put_line (sqlstmt);

                      EXECUTE IMMEDIATE sqlstmt;

                      kb1.next(i);

                  END IF;

                   

                  EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                  DBMS_OUTPUT.put_line ('No Data Found');

                  END;

                  /