Message-Id: <22528.293318@fatcity.com> From: "Aponte, Tony" Date: Tue, 10 Sep 2002 11:32:08 -0400 Subject: 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@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@competitrack.com 212-604-0200 x106 --=20 Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20 Author: Alan Davey INET: adavey@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@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 RE: Table Locks

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

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@competitrack.com
212-604-0200  x106



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