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).
Received on Thu Aug 29 2002 - 21:43:21 CDT
