Re: Table Locks

Thanks Rachel.

I spent the train ride reading the chapters on Instance Tuning and Dynamic Performance Views hoping to find something, but no such luck. I learned a lot of other useful things though, so it wasn't a waste of time.

Jacques, v$locked_object shows the table, but I already knew which table was locked. I was hoping to find the offending SQL statement.

Have a great weekend everyone.

Regards,

-- 

Alan Davey
adavey_at_competitrack.com
212-604-0200  x106


On 8/29/2002 10:43 PM, Rachel Carmichael <wisernet100_at_yahoo.com> wrote:


>I'm not sure it's possible to find the locking SQL and SID once the

>session issues other SQL statements.

>

>I spent a lot of time a few years back attempting to find it, without

>success. I got the people at both Platinum Technology and Savant 

>(yes,

>I'm showing my age here) to try to find it as well, figuring their

>technical people were better at this sort of thing than I am... no

>luck.

>

>I don't think Oracle stores the statement and who issued it, just 

>the

>rollback info necessary and the fact that there is a lock.

>

>

>--- Alan Davey <adavey_at_competitrack.com> wrote:

>> 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: http://www.orafaq.com

>> --

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

>

>

>__________________________________________________

>Do You Yahoo!?

>Yahoo! Finance - Get real-time stock quotes

>http://finance.yahoo.com

>-- 

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

>-- 

>Author: Rachel Carmichael

>  INET: wisernet100_at_yahoo.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: 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).