A Way to Resolve a Lock Conflict

The following code generates two lines. The first is a prompt to say what is going to happen, and the second is a 'KILL SESSION' statement.

Which session needs to be killed is determined by the Blocking_Session from V$Session. This query does not actually run that line -- it just generates it. After running this, the DBA can copy and paste the line as shown on the screen, or run KL.SQL to kill the session.

rem +--------------------------------------------------------------------------+
rem | Script ID:     KillLock.sql
rem | Purpose:       Resolve Lock Conflicts
rem |               
rem | Developer:     Lynn Tobias
rem | Script Date:   September 23, 2008
rem | Oracle Ver:    10g
rem |
rem | Table(s) Used: V$Session 
rem | Output:        KL.SQL 
rem +--------------------------------------------------------------------------+
Set Echo Off
Set Heading Off Linesize 300 Feedback Off
Column Line Format A300
--------------------------------------------------------------------------------
-- Display the line and build a sql file that looks like:
-- -- SCOTT's session will be killed
-- Alter System Kill Session '140,2540' Immediate;
--
-- Note: Cut and paste, or type '@KL' to run.
--------------------------------------------------------------------------------
Spool KL.Sql

Select   '-- '||Username||'''s session will be killed'
     || chr(10)
     || 'Alter System Kill Session '''||Sid||','||Serial#||''' Immediate;'
      as Line
 From V$Session
Where Sid In
            ----------------------------
            (Select Blocking_Session
               From V$Session
            )
            ----------------------------
;

spool off

Prompt
Prompt Note: Cut and paste, or type '@KL' to run.
Prompt

Set Feedback On Echo On Heading On