8 Replies Latest reply on Oct 23, 2010 4:58 PM by Billy~Verreynne

    Script to kill blocking sessions

      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.
        • 1. Re: Script to kill blocking sessions
          William Robertson
          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.
          1 person found this helpful
          • 2. Re: Script to kill blocking sessions
            Thanks, I see that V$session has what I need.
            In the following example:
            cursor A is
            select blocking_session
            where blocking_session is not null
            group by blocking_session;
            for a1 in a loop
            end loop
            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.
            • 3. Re: Script to kill blocking sessions
              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
                alter system kill session 'sid,serial#' immediate;


                You will find some more 'killing scripts' by doing a search yourself on http://asktom.oracle.com
                • 5. Re: Script to kill blocking sessions
                  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
                    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
                      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):
                          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%'
                              EXECUTE IMMEDIATE r.ddl
                          END LOOP;
                      • 8. Re: Script to kill blocking sessions
                        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>+