This script displays locks and gives the SID and Serial Number of the 
session to kill.

Execution Environment:
     SQL, SQL*Plus

Access Privileges:
     Requires SELECT on V$LOCK, V$SESSION, SYS.USER1, SYS.OBJ$.

Usage:
     sqlplus user/

Instructions:
     Copy the script to a file and execute it from SQL*Plus.

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail packages, and operating systems handle text formatting (spaces, 
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.

The report generated by this script gives information on sessions which are 
holding locks and gives the information needed to kill using the ALTER SYSTEM
KILL SESSION command. 

SET ECHO off 
REM NAME:   TFSLKILL.SQL 
REM USAGE:"@path/tfslkill" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    SELECT on V$LOCK, V$SESSION, SYS.USER$, SYS.OBJ$ 
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    The report generated by this script gives information on sessions 
REM    which are holding locks and gives the information needed to kill 
REM    using the ALTER SYSTEM KILL SESSION command. 
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
 
set linesize 132 pagesize 66 
break on Kill on username on terminal 
column Kill heading 'Kill String' format a13 
column res heading 'Resource Type' format 999 
column id1 format 9999990 
column id2 format 9999990 
column lmode heading 'Lock Held' format a20 
column request heading 'Lock Requested' format a20 
column serial# format 99999 
column username  format a10  heading "Username" 
column terminal heading Term format a6 
column tab format a35 heading "Table Name" 
column owner format a9 
column Address format a18 
column ctime heading "Seconds"
select	nvl(S.USERNAME,'Internal') username, 
	nvl(S.TERMINAL,'None') terminal, 
	L.SID||','||S.SERIAL# Kill, 
	U1.NAME||'.'||substr(T1.NAME,1,20) tab, 
	decode(L.LMODE,1,'No Lock', 
		2,'Row Share', 
		3,'Row Exclusive', 
		4,'Share', 
		5,'Share Row Exclusive', 
		6,'Exclusive',null) lmode, 
	decode(L.REQUEST,1,'No Lock', 
		2,'Row Share', 
		3,'Row Exclusive', 
		4,'Share', 
		5,'Share Row Exclusive', 
		6,'Exclusive',null) request,
        l.ctime
from	V$LOCK L,  
	V$SESSION S, 
	SYS.USER$ U1, 
	SYS.OBJ$ T1 
where	L.SID = S.SID  
and	T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)  
and	U1.USER# = T1.OWNER# 
and	S.TYPE != 'BACKGROUND' 
order by 1,2,5 
/ 
 
 
 
==============
Sample Output: 
==============
 
 
Username   Term   Kill String   Table Name            Lock Hld  Lock Req Seconds
---------- ------ ------------- --------------------- -------- --------- -------
SCOTT      ttyp9  7,5159        SCOTT.TABLE_CONFIG    Row Excl 
SCOTT      ttyr5  8,941         SCOTT.TT              Row Excl 
SYS        ttyq3  6,8885        SYS.CLUSTERS          Exclusiv 
                                SCOTT.TABLE_CONFIG    Row Excl 
SYSTEM     ttyq4  11,6593       SCOTT.TABLE_CONFIG    Row Excl 
SYSTEM     ttyr6  12,253        SYS.SYSINDEXES_       Exclusiv 
                                SCOTT.TT              Row Excl 
USUPPORT   ttyp1  9,6081        SYS.SYSEXTENTS        Exclusiv

EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.

IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.