Re: LOCK STATEMENT

<selvagam_at_my-deja.com> wrote in message news:7v55qb$lk8$1_at_nnrp1.deja.com...
> Please modify for your needs. Hope this helps you.
>
> Query
> ======
> select distinct ses.sid, ses.serial#, ses.machine,
> ses.username||'('||ses.sid||')' users, acc.owner owner,
> acc.object object, ses.lockwait, txt.sql_text sqltext
> from v$sqltext txt, v$access acc, v$session ses, v$lock
> where txt.address = ses.sql_address
> and txt.hash_value = ses.sql_hash_value
> and ses.sid = acc.sid
> and ses.lockwait is not null
> and ses.sid = v$lock.sid
> and upper(txt.sql_text) like '%UPDATE%'

Well, neither Stephan Born's solution and yours work.

v$access is not used to find locked objects, use v$lock or v$locked_object instead.

v$sql, v$sqlare, v$sqltext... are not helpful, since users may share the same SQL statements.

v$open_cursor is not helpful, since the cursor is closed when the SQL statement is completed.

IMHO, there is no way to clearly find out which statements cause the specific lock, since Oracle keeps recording the changed data, but not the statements making the change. Received on Thu Oct 28 1999 - 00:00:00 CEST