Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Table Locks
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>
<!-- Converted from text/plain format -->
<P><FONT SIZE=3D2>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.</FONT></P>
<P><FONT SIZE=3D2>HTH</FONT>
<BR><FONT SIZE=3D2>Tony Aponte</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Alan Davey [<A =
HREF=3D"mailto:adavey_at_competitrack.com">mailto:adavey_at_competitrack.com</A=
>]</FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, August 29, 2002 4:44 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Table Locks</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>Hi All,</FONT>
</P>
<P><FONT SIZE=3D2>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.</FONT></P>
<P><FONT SIZE=3D2>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 </FONT></P>
<P><FONT SIZE=3D2>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?</FONT></P>
<P><FONT SIZE=3D2>I'm RTFMing, but so far no luck. Any help would =
be greatly appreciated.</FONT>
</P>
<P><FONT SIZE=3D2>Regards,</FONT>
<BR><FONT SIZE=3D2>-- </FONT>
</P>
<P><FONT SIZE=3D2>Alan Davey</FONT>
<BR><FONT SIZE=3D2>adavey_at_competitrack.com</FONT>
<BR><FONT SIZE=3D2>212-604-0200 x106</FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Alan Davey</FONT>
<BR><FONT SIZE=3D2> INET: adavey_at_competitrack.com</FONT>
</P>
<P><FONT SIZE=3D2>Fat City Network Services -- (858) =
538-5051 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>----------------------------------------------------------------=----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =
from). You may</FONT>
Received on Tue Sep 10 2002 - 10:32:08 CDT
![]() |
![]() |