find the sql cause lock,I use the sql (uncommit or rollback sql statement)

[xiongye_2]

Hi: I want to find the sql cause lock,I use the sql

SELECT object_name,SESSION_ID,SERIAL#,machine,
osuser,S.PROCESS,e.sql_text
FROM V$LOCKED_OBJECT p, V$SESSION S , dba_objects B, v$sql e, v$lock l
WHERE p.SESSION_ID=S.SID and b.OBJECT_ID=p.OBJECT_ID and
l.addr=e.ADDRESS
and l.sid=s.sid;
but $lock 's addr and v$sql 's address is not equal?
or
How can I find the sql cause lock ?

Tks??????

[Jeanine Kone]

You can join v$session to v$sql as shown below.
This may eliminate the need to join v$lock with v$sql.

select s.username, s.sid, t.sql_text "Last SQL"
from v$session s, v$sql t
where s.sql_address =t.address and s.sql_hash_value =t.hash_value

Hope this helps, Jeanine

[Scott Benitez]

Change l.addr to s.sql_address

SELECT object_name, SESSION_ID, SERIAL#, machine, osuser, S.PROCESS, e.sql_text
FROM V$LOCKED_OBJECT p, V$SESSION S , DBA_OBJECTS B, v$sql e, v$lock l
WHERE p.SESSION_ID=S.SID AND b.OBJECT_ID=p.OBJECT_ID AND
s.sql_address=e.ADDRESS
AND l.sid=s.sid;

I also have a sql script I modified to givel sql output. The formatting is questionable (but it serves my needs). It uses dbms_output so keep in mind the upper bound of the buffer. It gives all the session info, spid, login time, status, ... It faster than trying to join all the tables together.

Scott