Re: How to get locked rows for a table ?

From: Joel Garry <joelga_at_rossinc.com>
Date: 1997/02/25
Message-ID: <1997Feb25.195219.22071@rossinc.com>#1/1

In article <01bc15d9$83500fc0$0200000a_at_pitfall> "Michael Agbaglo" <byteshif_at_cs.tu-berlin.de> writes:
>Looking for a SELECT statement or a PROCEDURE that returns ROWIDs from
>locked rows for a given table. I would do it myself but I don't have enough
>info on the tables ending with $.
>
>Please don't tell me to use SELECT ... NOWAIT - That's the (most?)
>inefficient way to do it: It needs two tablescans...
>
>replies via email preferred - thanks !
>
>

The following is on the Proceedings of the International Oracle Week '96, which is, unfortunately, in the stupid Acrobat format. I hope it hasn't lost anything in the translation, I can't try it right now. From a talk by RaviKumar Ramamurthy (assumes 7.2.2). Basically, the views v$session, v$lock and all_objects have the information you want.

Paper #5 Page 2
The Unix Korn Script used is :

############################################################## 
# 
# Name : wholock.sh 
# Input : Oracle User Id 
# Purpose : To Locate which resource is the Input User ID 
# Waiting for 

#

############################################################## 

GetUserId()
{
 echo "Enter the Waiting User Id : \c"
 read WaitingUser
 WaitingUser=`echo ${WaitingUser} | tr "[a-z]" "[A-Z]"` }
GetLockedTableName()
{

	echo " set pages 1000 " > tbl.sql 
	echo " set head off " >> tbl.sql 
	echo " select 'XXX+'||object_name||'+'||row_wait_block#||'+'||" >> tbl.sql 
	echo "row_wait_row#||'+'||row_wait_file#||'+'||sid||'+'||serial#||'+' " >> tbl.sql 
	echo ' from all_objects, v$session ' >> tbl.sql 
	echo " where row_wait_obj# = object_id " >> tbl.sql 
	echo " and type = 'USER' " >> tbl.sql 
	echo " and username = '${WaitingUser}' " >> tbl.sql 
	echo " and lockwait is not null ;" >> tbl.sql
 	LockExists=`${SQL} < tbl.sql |grep "XXX"|wc -l`
 	if [ ${LockExists} -eq 0 ] 
	   then 
		echo "The User ${WaitingUser} is not waiting For any Resources" 
		exit 
	   else ObjStr=`${SQL} < tbl.sql |grep "XXX"` 
		TableName=`echo ${ObjStr} | cut -f2 -d"+"` 
		WaitingUserSid=`echo ${ObjStr} | cut -f6 -d"+"` 
		WaitingUserSrl=`echo ${ObjStr} | cut -f7 -d"+"` 
	fi 

}
GetLockedRowId()
{

	Block=`echo ${ObjStr} | cut -f3 -d"+"` 
	Row=`echo ${ObjStr} | cut -f4 -d"+"` 
	File=`echo ${ObjStr} | cut -f5 -d"+"` 
	HexBlock=`echo ${Block} | awk '{printf"%x\n",$1}' | awk '{printf"%08s",$1}` 
	HexRow=`echo ${Row} | awk '{printf"%x\n",$1}' | awk '{printf"%08s",$1}` 
	HexFile=`echo ${File} | awk '{printf"%x\n",$1}' | awk '{printf"%08s",$1}` 
	RowId=${HexBlock}.${HexRow}.${HexFile} 

}
GetUserLocking()
{

	echo "set pages 1000" > u1.sql 
	echo "set head off" >> u1.sql 
	echo "select 'XXX+'||s1.username||'+'||s1.sid||'+'||s1.serial#||'+'" >> u1.sql 
	echo " from v\$lock l1, v\$session s1 where exists " >> 
u1.sql 
	echo " (select 'x' from v\$lock l2, v\$session s2 " >> u1.sql 
	echo "where l2.sid = s2.sid and l2.id1 = l1.id1 " >> u1.sql 
	echo "and s2.lockwait = l2.kaddr and username = '${WaitingUser}') " >> u1.sql 
	echo "and request = 0 and l1.sid = s1.sid ;" >> u1.sql 
	LockingStr=`${SQL} < u1.sql | grep "XXX"` 
	LockingUser=`echo ${LockingStr} | cut -f2 -d"+"` 
	LockingUserSid=`echo ${LockingStr} | cut -f3 -d"+"` 
	LockingUserSrl=`echo ${LockingStr} | cut -f4 -d"+"` 

}
DisplayResult()
{

	echo "The User ${WaitingUser} (sid=${WaitingUserSid} and serial# = ${WaitingUserSrl}) is waiting to Acquire Resource " 
	echo "which is currently held by ${LockingUser} (sid=${LockingUserSid} and serial# ${LockingUserSrl})" 
	echo "on the table ${TableName} with the Rowid = ${RowId}" 

}

Main()
{

	GetUserId 
	GetLockedTableName 
	GetLockedRowId 
	GetUserLocking 
	DisplayResult 

}
SQL="sqlplus -s monitor/monitor"
Main

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O

Received on Tue Feb 25 1997 - 00:00:00 CST