kill blocking sessions

[Roshan]

Oracle Database 12.1.0.2
RHEL 6

Hi Erman,

I see there are lots of blocking sessions(>100) on my production database.

I  have created a procedure to kill these sessions.

Can you please help me with the procedure?


DECLARE

    CURSOR mycursorsession IS

      SELECT sid,

             serial#

      FROM   v$session

      WHERE  blocking_session_status = 'VALID';

    cur_bir_rec_session mycursorsession%ROWTYPE;

BEGIN

    OPEN mycursorsession;

 

    LOOP

        FETCH mycursorsession INTO cur_bir_rec_session;

 

        EXIT WHEN mycursorsession%NOTFOUND;

 

        EXECUTE IMMEDIATE 'Alter System Kill Session '|| cur_bir_rec_session.sid

        ||

        ','||

        cur_bir_rec_session .serial#||'  IMMEDIATE;';

    END LOOP;

END;

 

/

EMQUEUED.PNG

Thanks,

Roshan