On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Monday, October 20, 2008

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

No comments:

Post a Comment