This discussion is archived
8 Replies Latest reply: Oct 23, 2010 9:58 AM by BillyVerreynne RSS

Script to kill blocking sessions

751187 Newbie
Currently Being Moderated
We use Oracle 10. We have a test database used solely for training and I need the give the trainer the ability to kill all the blocking sessions when a block(s) is holding up
the entire class.
My idea was to create a simple form on our Oracle menu- containing just a single button that when pressed would trigger the script. One press of the button
to kill each and every blocking session. I have no clue as to how to write a script of this nature. Would appreciate any advice.
Regards,
Sandy
  • 1. Re: Script to kill blocking sessions
    William Robertson Oracle ACE
    Currently Being Moderated
    Look at the BLOCKING_SESSION column in V$SESSION.

    Whether you create a "form", a "script" or a procedure will depend on how your "Oracle menu" is written. Perhaps the trainer can advise.
  • 2. Re: Script to kill blocking sessions
    751187 Newbie
    Currently Being Moderated
    Thanks, I see that V$session has what I need.
    In the following example:
    -----
    cursor A is
    select blocking_session
    fromV$session
    where blocking_session is not null
    group by blocking_session;
    ..
    ..
    for a1 in a loop
    xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    end loop
    end
    ----
    What is the command and syntax I would use for xxxxxxxxxxxxxxxxxxxxxxx?
    __
    Would I need a "WHERE sid = a.blocking_session" statement
    or would I be able to embed a.blocking_session into the command line?
    __
    I currently use TOAD for Oracle to kill sessions and I allways have the "Immediate"
    checkbox checked, so the command in the script I'd like to have immediate also.
    __
    Thanks
    Sandy
  • 3. Re: Script to kill blocking sessions
    Saubhik Guru
    Currently Being Moderated
    You can use something like this
    SQL> DECLARE
      2  CURSOR c IS
      3  SELECT c.owner,
      4        c.object_name,
      5        c.object_type,
      6        b.SID,
      7        b.serial#,
      8        b.status,
      9        b.osuser,
     10        b.machine
     11   FROM v$locked_object a, v$session b, dba_objects c
     12   WHERE b.SID = a.session_id AND a.object_id = c.object_id
     13   and c.object_name in ('JSW_CRM_C_HR_COIL_INFO','T_SPCL_BARCODE_CRS2');
     14  c_row c%ROWTYPE;
     15  l_sql VARCHAR2(100);
     16  BEGIN
     17  OPEN c;
     18  LOOP
     19  FETCH c INTO c_row;
     20  EXIT WHEN c%NOTFOUND;
     21  l_sql := 'alter system kill session '''||c_row.SID||','||c_row.serial#||'''';
     22  EXECUTE IMMEDIATE l_sql;
     23  END LOOP;
     24  CLOSE c;
     25  END;
     26  / 
    Do not forget to use your own SQL statement in the CURSOR!.
  • 4. Re: Script to kill blocking sessions
    Hoek Guru
    Currently Being Moderated
    alter system kill session 'sid,serial#' immediate;

    http://www.oracle-base.com/articles/misc/KillingOracleSessions.php
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1419803982336

    You will find some more 'killing scripts' by doing a search yourself on http://asktom.oracle.com
  • 5. Re: Script to kill blocking sessions
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    This is easier said than done. Numerous reasons. Here's a couple:

    You cannot rely on using v$session to identifying blocked sessions.

    Some user sessions may be blocked by a system process (e.g. log writer), which means you cannot unblock the user sessions by killing the blocking session. (especially when running into an Oracle bug where a user session will spin on "+enq: RO - fast object reuse+" when dropping an object and then being shown as blocked by a system session/process).

    Killing a session in Oracle is a misnomer as the "+alter system kill session+" is a request and not a command. The relevant user session needs to terminate itself (kind of like assisted suicide). No other process actually kills the physical process (or thread) executing that user session. Thus if that user session is waiting on some external response or event, it will be unable (now and forever) to respond to that kill request. In such a case, the only alternative is killing the physical process instead. (or shutting down the database)

    A blocking session could be a job process that contains problematic code that leads to the blocking/serialisation. Killing that session will simply fail the job after which the job queue manager will restart the session again.. 16 times in succession. Only then will it mark that job entry as broken and cease trying to restart it.

    So killing blocking sessions? It is not easy to identify blocking processes, they cannot always be killed, and even when you do kill them, they may simply reincarnate as a new session.

    So what is the solution then?

    Blocking sessions are not "+A Bad Thing+". There are very good and technically sound reasons for serialisation. When it does become a problem, there's something more to it. And blindly killing blocking sessions in such a case is treating the symptom of a problem and ignoring the problem all together. Find the reason for this serialisation and address that.
  • 6. Re: Script to kill blocking sessions
    751187 Newbie
    Currently Being Moderated
    Looks like I have some homework to do first.
    Thanks for everyone's input into this.
    __
    Since this project is for our test training database, I can do some experimenting
    without doing much harm. The live databases, well that's another story,
    maybe down the road.
  • 7. Re: Script to kill blocking sessions
    William Robertson Oracle ACE
    Currently Being Moderated
    True enough, however given the requirement to <i>"give the trainer the ability to kill all the blocking sessions when a block(s) is holding up the entire class"</i>, in a training environment that could presumably be restarted or reinstalled from scratch without causing major problems, I don't think it's that unreasonable to implement a kill-'em-all button. Presumably the query of v$session could be refined to include only foreground sessions logged in as a particular user, e.g. (untested):
    BEGIN
        FOR r IN (
            SELECT 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE' AS ddl
            FROM   v$session s
            AND    s.blocking_session IS NOT NULL
            AND    s.username LIKE 'TRAIN%'
        )
        LOOP
            DBMS_OUTPUT.PUT_LINE(r.ddl);
            EXECUTE IMMEDIATE r.ddl
        END LOOP;
    END;
  • 8. Re: Script to kill blocking sessions
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    So you're not allowed to resort to the old lead pipe to "fix" problems in the class when dealing with a "problem" caused by a student? +<innocent expression>+

    ;-)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points