Script to kill blocking sessions

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.

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!.

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

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.

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;

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>+

;-)