RE: who locked table??

From: <lhoska_at_calibresys.com>
Date: Fri, 01 Jun 2001 11:24:30 -0700
Message-ID: <F001.00318273.20010601100648@fatcity.com>

select username, sid, serial#, status from v$session; alter system kill session \'<sid>,<serial#>\';

Also, you can use ORAKILL utility.

select sid, spid thread, osuser, s.program from sys.v_$process p, sys.v_$session s where p.addr = s.paddr;
C:\>orakill <sid> <thread>

The script below gives you a lot of detailed information about your lock.

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

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
/

-----Original Message-----
Sent: Friday, June 01, 2001 10:55 AM
To: Multiple recipients of list ORACLE-L

Hi Friends

Does any body have script to see who locked the table and subsequently If needy to kill the user?? I had it but I want to update mine.

TIA
Raghu.

Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Raghu Kota
INET: raghukota_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: lhoska_at_calibresys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------