RE: Table Locks

From: Aponte, Tony <AponteT_at_hsn.net>
Date: Tue, 10 Sep 2002 11:32:08 -0400
Message-Id: <22528.293318@fatcity.com>

This is a multi-part message in MIME format.

------_=_NextPart_001_01C258DF.39A6312C
Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Call me crazy if you wish. But I would take a process or system state = dump and navigate the locking session's object hierarchy. Yes, I know, = ugly as Sin and potentially life-shortening.

HTH
Tony Aponte

-----Original Message-----
From: Alan Davey [mailto:adavey_at_competitrack.com] Sent: Thursday, August 29, 2002 4:44 PM
To: Multiple recipients of list ORACLE-L Subject: Table Locks

Hi All,

I've noticed some locks on various tables and I'm trying to figure out = which DML statements are causing the locks. In this example, the lock = isn't being released because the developer forgot to include a = commit/rollback.

If I look at v$session which is causing the lock and query v$sqlarea = with the values in sql_address and prev_sql_addr, I only see select = statements that were issued after the DML (in this case a delete). I = can query=20
v$sqlarea with the locked table name and find the delete statement, but = how do I link this back to the sid that issued it? Also, what if there = had been multiple DML statements by this user, how would I know which = was the first/last one executed?

I'm RTFMing, but so far no luck. Any help would be greatly appreciated.

Regards,
--=20

Alan Davey
adavey_at_competitrack.com
212-604-0200 x106

--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20
Author: Alan Davey
INET: adavey_at_competitrack.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).

------_=_NextPart_001_01C258DF.39A6312C
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.0.6249.1">
<TITLE>RE: Table Locks</TITLE>
</HEAD>
<BODY>


Call me crazy if you wish.  But I would take a =
process or system state dump and navigate the locking session's object = hierarchy.  Yes, I know, ugly as Sin and potentially = life-shortening.

HTH

 Tony Aponte


-----Original Message-----

 From: Alan Davey [<A =
HREF=3D"mailto:adavey_at_competitrack.com">mailto:adavey_at_competitrack.com</A= >]

 Sent: Thursday, August 29, 2002 4:44 PM

 To: Multiple recipients of list ORACLE-L

 Subject: Table Locks

 

Hi All,


I've noticed some locks on various tables and I'm =
trying to figure out which DML statements are causing the locks.  = In this example, the lock isn't being released because the developer = forgot to include a commit/rollback.

If I look at v$session which is causing the lock and =
query v$sqlarea with  the values in sql_address and prev_sql_addr, = I only see select statements that were issued after the DML (in this = case a delete).  I can query

v$sqlarea with the locked table name and find the =
delete statement, but how do I link this back to the sid that issued = it?  Also, what if there had been multiple DML statements by this = user, how would I know which was the first/last one executed?

I'm RTFMing, but so far no luck.  Any help would =
be greatly appreciated.


Regards,

 -- 


Alan Davey

 adavey_at_competitrack.com

 212-604-0200  x106

 
 

-- 

 Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com">http://www.orafaq.com</A>

 -- 

 Author: Alan Davey

   INET: adavey_at_competitrack.com


Fat City Network Services    -- (858) =
538-5051  FAX: (858) 538-5051

 San Diego, =

California        -- Public Internet = access / Mailing Lists

 <FONT =

SIZE=3D2>----------------------------------------------------------------=

----

 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 Received on Tue Sep 10 2002 - 10:32:08 CDT