Re: Oracle Lock Problem(B

From: ddf(B <oratune_at_msn.com>
Date: Fri, 27 Mar 2009 08:36:41 -0700 (PDT)
Message-ID: <d0b7e786-f0b8-41dc-a07d-c8736fd97f8c_at_b16g2000yqb.googlegroups.com>

On Mar 27, 10:31 am, ddf <orat..._at_msn.com> wrote:
> On Mar 27, 10:04 am, Ming$BGl(B <l..._at_tech-trans.com> wrote:
>
> > Hi,
>
> > How can I find the SQL statement that make the table lock?
>
> > Ming
>
> You can try this:
>
> select sql_fulltext
> from v$sqlarea
> where (address, hash_value) in
> (select sql_address, sql_hash_value
> from v$session
> where sid in (select session_id from v$locked_object))
> /
>
> Or you can use OEM or TOAD to show you that information.
>
> David Fitzjarrell

Actually this works better:

select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where sid in (select session_id from v$locked_object)) union
select sql_text
from v$sqlarea
where (address, hash_value) in
(select prev_sql_addr, prev_hash_value
from v$session
where sid in (select session_id from v$locked_object)) /

David Fitzjarrell Received on Fri Mar 27 2009 - 10:36:41 CDT